﻿
GO
PRINT N'Creating [Analytics]...';


GO
CREATE SCHEMA [Analytics]
    AUTHORIZATION [dbo];


GO
PRINT N'Creating [DataConsistency]...';


GO
CREATE SCHEMA [DataConsistency]
    AUTHORIZATION [dbo];


GO
PRINT N'Creating [iComply]...';


GO
CREATE SCHEMA [iComply]
    AUTHORIZATION [dbo];


GO
PRINT N'Creating [iPerform]...';


GO
CREATE SCHEMA [iPerform]
    AUTHORIZATION [dbo];


GO
PRINT N'Creating [Membership]...';


GO
CREATE SCHEMA [Membership]
    AUTHORIZATION [dbo];


GO
PRINT N'Creating [Membership_TradingData]...';


GO
CREATE SCHEMA [Membership_TradingData]
    AUTHORIZATION [dbo];


GO
PRINT N'Creating [Reports]...';


GO
CREATE SCHEMA [Reports]
    AUTHORIZATION [dbo];


GO
PRINT N'Creating [Settings]...';


GO
CREATE SCHEMA [Settings]
    AUTHORIZATION [dbo];


GO
PRINT N'Creating [System]...';


GO
CREATE SCHEMA [System]
    AUTHORIZATION [dbo];


GO
PRINT N'Creating [TradingData]...';


GO
CREATE SCHEMA [TradingData]
    AUTHORIZATION [dbo];


GO
PRINT N'Creating [IIP]...';


GO
CREATE FULLTEXT CATALOG [IIP]
    WITH ACCENT_SENSITIVITY = OFF
    AUTHORIZATION [dbo];


GO
PRINT N'Creating [Analytics].[IdeaToTrade]...';


GO
CREATE TABLE [Analytics].[IdeaToTrade] (
    [Id]      INT              IDENTITY (1, 1) NOT NULL,
    [IdeaId]  UNIQUEIDENTIFIER NOT NULL,
    [TradeId] INT              NOT NULL,
    PRIMARY KEY CLUSTERED ([Id] ASC)
);


GO
PRINT N'Creating [Analytics].[IdeaToSecurity]...';


GO
CREATE TABLE [Analytics].[IdeaToSecurity] (
    [IdeaId]           UNIQUEIDENTIFIER NOT NULL,
    [SecurityId]       INT              NOT NULL,
    [IsMasterSecurity] BIT              NOT NULL,
    CONSTRAINT [PK_IdeaToSecurity] PRIMARY KEY CLUSTERED ([IdeaId] ASC, [SecurityId] ASC)
);


GO
PRINT N'Creating [Analytics].[Idea]...';


GO
CREATE TABLE [Analytics].[Idea] (
    [Id]               UNIQUEIDENTIFIER NOT NULL,
    [HasOpenPosition]  BIT              NOT NULL,
    [FundBookEntityId] INT              NOT NULL,
    [Type]             SMALLINT         NULL,
    [Name]             NVARCHAR (150)   NOT NULL,
    PRIMARY KEY CLUSTERED ([Id] ASC)
);


GO
PRINT N'Creating [Analytics].[TradedQuantity]...';


GO
CREATE TABLE [Analytics].[TradedQuantity] (
    [Id]           INT      IDENTITY (1, 1) NOT NULL,
    [TradeId]      INT      NULL,
    [Quantity]     MONEY    NOT NULL,
    [Date]         DATETIME NOT NULL,
    [InvestmentId] INT      NOT NULL,
    PRIMARY KEY CLUSTERED ([Id] ASC)
);


GO
PRINT N'Creating [Analytics].[InvestmentSerie]...';


GO
CREATE TABLE [Analytics].[InvestmentSerie] (
    [InvestmentId]       INT              NOT NULL,
    [Date]               DATE             NOT NULL,
    [SecurityId]         INT              NOT NULL,
    [AgeByTimeSeries]    INT              NOT NULL,
    [MarketCapSum]       DECIMAL (29)     NOT NULL,
    [Weight]             DECIMAL (29, 20) NOT NULL,
    [Risk]               DECIMAL (29, 20) NOT NULL,
    [Return]             DECIMAL (29, 20) NOT NULL,
    [RoC]                DECIMAL (29, 20) NOT NULL,
    [CloseQuantity]      MONEY            NOT NULL,
    [CloseExposure]      MONEY            NOT NULL,
    [PositionPnL]        DECIMAL (18, 4)  NOT NULL,
    [PositionFxPnL]      DECIMAL (18, 4)  NOT NULL,
    [TradePnL]           DECIMAL (18, 4)  NOT NULL,
    [TradeFxPnL]         DECIMAL (18, 4)  NOT NULL,
	[PnL]                AS               ((([TradePnL] + [PositionPnL]) + [TradeFxPnL]) + [PositionFxPnL]),
    [BenchmarkId]        INT              NOT NULL,
    [TracePoint]         DECIMAL (18, 10) NOT NULL,
    [TracePointWeighted] DECIMAL (18, 10) NOT NULL,
    CONSTRAINT [PK_InvestmentSerie] PRIMARY KEY CLUSTERED ([InvestmentId] ASC, [Date] ASC, [SecurityId] ASC)
);


GO
PRINT N'Creating [Analytics].[Investment]...';


GO
CREATE TABLE [Analytics].[Investment] (
    [Id]               INT              NOT NULL,
    [StartDate]        DATE             NOT NULL,
    [EndDate]          DATE             NOT NULL,
    [Side]             INT              NOT NULL,
    [IsActive]         BIT              NOT NULL,
    [FundBookEntityId] INT              NULL,
    [IdeaId]           UNIQUEIDENTIFIER NOT NULL,
    [Identity]         INT              IDENTITY (1, 1) NOT NULL,
    [CumulativeReturn] DECIMAL (29, 20) NOT NULL,
    [CumulativeRoC]    DECIMAL (29, 20) NOT NULL,
    [AvarageWeight]    DECIMAL (29, 20) NOT NULL,
    CONSTRAINT [PK__Investment__3214EC073B4BBA2E] PRIMARY KEY CLUSTERED ([Id] ASC)
);


GO
PRINT N'Creating [Analytics].[TracePoint]...';


GO
CREATE TABLE [Analytics].[TracePoint] (
    [InvestmentId] INT              NOT NULL,
    [Date]         DATE             NOT NULL,
    [Value]        DECIMAL (29, 20) NULL,
    CONSTRAINT [PK_TracePoint] PRIMARY KEY CLUSTERED ([InvestmentId] ASC, [Date] ASC)
);


GO
PRINT N'Creating [Analytics].[UiaEntry]...';


GO
CREATE TABLE [Analytics].[UiaEntry] (
    [Id]            INT      IDENTITY (1, 1) NOT NULL,
    [ProcessedDate] DATE     NOT NULL,
    [Timestamp]     DATETIME NULL,
    PRIMARY KEY CLUSTERED ([Id] ASC)
);


GO
PRINT N'Creating [Analytics].[UiaEntry].[IX_UiaEntry_ProcessedDate]...';


GO
CREATE NONCLUSTERED INDEX [IX_UiaEntry_ProcessedDate]
    ON [Analytics].[UiaEntry]([ProcessedDate] ASC);


GO
PRINT N'Creating [DataConsistency].[SuspiciousExposure]...';


GO
CREATE TABLE [DataConsistency].[SuspiciousExposure] (
    [Id]                  INT             IDENTITY (1, 1) NOT NULL,
    [FundBookEntityId]    INT             NOT NULL,
    [SecurityId]          INT             NOT NULL,
    [Date]                DATE            NOT NULL,
    [SuggestedExposure]   DECIMAL (18, 4) NULL,
    [DifferencePercent]   DECIMAL (18, 4) NULL,
    [ProcessingTimestamp] DATETIME        NOT NULL,
    PRIMARY KEY CLUSTERED ([Id] ASC)
);


GO
PRINT N'Creating [iComply].[Comments]...';


GO
CREATE TABLE [iComply].[Comments] (
    [Id]       INT      IDENTITY (1, 1) NOT NULL,
    [PeriodId] INT      NOT NULL,
    [Author]   INT      NOT NULL,
    [Text]     NTEXT    COLLATE Latin1_General_CI_AS NOT NULL,
    [Time]     DATETIME NOT NULL,
    [ParentId] INT      NULL,
    CONSTRAINT [PK_Comments] PRIMARY KEY CLUSTERED ([Id] ASC)
);


GO
PRINT N'Creating [iComply].[IncludedEventSecurityClass]...';


GO
CREATE TABLE [iComply].[IncludedEventSecurityClass] (
    [SecurityClassId] INT            NOT NULL,
    [EventType]       NVARCHAR (256) NOT NULL,
    CONSTRAINT [PK_IncludedEventSecurityClass] PRIMARY KEY CLUSTERED ([SecurityClassId] ASC, [EventType] ASC)
);


GO
PRINT N'Creating [iComply].[ComplianceEvent]...';


GO
CREATE TABLE [iComply].[ComplianceEvent] (
    [Id]           INT            IDENTITY (1, 1) NOT NULL,
    [SecurityId]   INT            NOT NULL,
    [Date]         DATETIME2 (7)  NOT NULL,
    [Determinator] NVARCHAR (255) NOT NULL,
    [Status]       SMALLINT       NOT NULL,
    [SigmaMove]    FLOAT (53)     NOT NULL,
    CONSTRAINT [PK_ComplianceEvent] PRIMARY KEY CLUSTERED ([Id] ASC)
);


GO
PRINT N'Creating [iComply].[CommentToFileUpload]...';


GO
CREATE TABLE [iComply].[CommentToFileUpload] (
    [CommentId]    INT NOT NULL,
    [FileUploadId] INT NOT NULL,
    PRIMARY KEY CLUSTERED ([CommentId] ASC, [FileUploadId] ASC)
);


GO
PRINT N'Creating [iComply].[SuspiciousActivity]...';


GO
CREATE TABLE [iComply].[SuspiciousActivity] (
    [Id]               INT              IDENTITY (1, 1) NOT NULL,
    [FundBookEntityId] INT              NOT NULL,
    [EventId]          INT              NOT NULL,
    [IsActive]         BIT              NOT NULL,
    [ExcessReturn]     DECIMAL (18, 10) NOT NULL,
    PRIMARY KEY CLUSTERED ([Id] ASC)
);


GO
PRINT N'Creating [iComply].[TradeUnderSurveillance]...';


GO
CREATE TABLE [iComply].[TradeUnderSurveillance] (
    [Id]                   INT        IDENTITY (1, 1) NOT NULL,
    [TradeId]              INT        NOT NULL,
    [SuspiciousActivityId] INT        NOT NULL,
    [TradeHeat]            FLOAT (53) NOT NULL,
    [WeightSum]            FLOAT (53) NOT NULL,
    CONSTRAINT [PK_TradeUnderSurveillance] PRIMARY KEY CLUSTERED ([Id] ASC)
);


GO
PRINT N'Creating [iComply].[TradeHeatItems]...';


GO
CREATE TABLE [iComply].[TradeHeatItems] (
    [ComplianceTradeId] INT            NOT NULL,
    [Type]              INT            NOT NULL,
    [OutlierScore]      FLOAT (53)     NOT NULL,
    [Weight]            FLOAT (53)     NOT NULL,
    [Value]             FLOAT (53)     NOT NULL,
    [Distribution]      NVARCHAR (MAX) COLLATE Latin1_General_CI_AS NULL,
    CONSTRAINT [PK_TradeHeatItems] PRIMARY KEY CLUSTERED ([ComplianceTradeId] ASC, [Type] ASC)
);


GO
PRINT N'Creating [iComply].[CompliancePeriodToSuspiciousActivity]...';


GO
CREATE TABLE [iComply].[CompliancePeriodToSuspiciousActivity] (
    [CompliancePeriodId]   INT NOT NULL,
    [SuspiciousActivityId] INT NOT NULL,
    CONSTRAINT [PK_CompliancePeriodToSuspiciousActivity] PRIMARY KEY CLUSTERED ([CompliancePeriodId] ASC, [SuspiciousActivityId] ASC)
);


GO
PRINT N'Creating [iComply].[CompliancePeriod]...';


GO
CREATE TABLE [iComply].[CompliancePeriod] (
    [Id]                INT     IDENTITY (1, 1) NOT NULL,
    [StartDate]         DATE    NOT NULL,
    [EndDate]           DATE    NOT NULL,
    [StatusId]          TINYINT NOT NULL,
    [OwnerId]           INT     NOT NULL,
    [IsAssignedByAdmin] BIT     NOT NULL,
    CONSTRAINT [PK_Period] PRIMARY KEY CLUSTERED ([Id] ASC)
);


GO
PRINT N'Creating [iPerform].[Widget]...';


GO
CREATE TABLE [iPerform].[Widget] (
    [Id]   UNIQUEIDENTIFIER NOT NULL,
    [Data] NVARCHAR (MAX)   NULL,
    CONSTRAINT [PK_Widget] PRIMARY KEY CLUSTERED ([Id] ASC)
);


GO
PRINT N'Creating [iPerform].[Layout]...';


GO
CREATE TABLE [iPerform].[Layout] (
    [Id]         INT            IDENTITY (1, 1) NOT NULL,
    [UserId]     INT            NOT NULL,
    [LayoutData] NVARCHAR (MAX) NOT NULL,
    CONSTRAINT [PK_Layout] PRIMARY KEY CLUSTERED ([Id] ASC)
);


GO
PRINT N'Creating [iPerform].[Template2PossibleAnswer]...';


GO
CREATE TABLE [iPerform].[Template2PossibleAnswer] (
    [TemplateId]       INT NOT NULL,
    [PossibleAnswerId] INT NOT NULL,
    CONSTRAINT [PK_Template2PossibleAnswer] PRIMARY KEY CLUSTERED ([TemplateId] ASC, [PossibleAnswerId] ASC)
);


GO
PRINT N'Creating [iPerform].[Template]...';


GO
CREATE TABLE [iPerform].[Template] (
    [Id]              INT            IDENTITY (1, 1) NOT NULL,
    [UserId]          INT            NULL,
    [QuestionGroupId] INT            NOT NULL,
    [Active]          BIT            NULL,
    [AddedDt]         DATETIME       NULL,
    [Name]            NVARCHAR (150) COLLATE Latin1_General_CI_AS NULL,
    [ParentId]        INT            NULL,
    [TypeId]          TINYINT        NULL,
    CONSTRAINT [PK_TemplateInstance] PRIMARY KEY CLUSTERED ([Id] ASC)
);


GO
PRINT N'Creating [iPerform].[IdeaToTemplateAnswer]...';


GO
CREATE TABLE [iPerform].[IdeaToTemplateAnswer] (
    [IdeaId]           UNIQUEIDENTIFIER NULL,
    [TemplateAnswerId] INT              NULL
);


GO
PRINT N'Creating [iPerform].[IdeaToSecurity]...';


GO
CREATE TABLE [iPerform].[IdeaToSecurity] (
    [IdeaId]           UNIQUEIDENTIFIER NOT NULL,
    [SecurityId]       INT              NOT NULL,
    [IsMasterSecurity] BIT              NOT NULL,
    CONSTRAINT [PK_IdeaToSecurity] PRIMARY KEY CLUSTERED ([IdeaId] ASC, [SecurityId] ASC)
);


GO
PRINT N'Creating [iPerform].[Idea]...';


GO
CREATE TABLE [iPerform].[Idea] (
    [Id]                     UNIQUEIDENTIFIER NOT NULL,
    [Closed]                 BIT              NULL,
    [FundBookEntityId]       INT              NULL,
    [OwnerUserId]            INT              NULL,
    [StrategyInitTemplateId] INT              NULL,
    [Private]                BIT              NULL,
    [Name]                   NVARCHAR (150)   COLLATE Latin1_General_CI_AS NULL,
    [AbsoluteRelative]       BIT              NULL,
    [Type]                   SMALLINT         NULL,
    PRIMARY KEY CLUSTERED ([Id] ASC)
);


GO
PRINT N'Creating [iPerform].[SkipTrades]...';


GO
CREATE TABLE [iPerform].[SkipTrades] (
    [Id]      INT IDENTITY (1, 1) NOT NULL,
    [TradeId] INT NOT NULL,
    CONSTRAINT [PK_SkipTrades] PRIMARY KEY CLUSTERED ([Id] ASC)
);


GO
PRINT N'Creating [iPerform].[SkipTrades].[TradeIdIndex]...';


GO
CREATE NONCLUSTERED INDEX [TradeIdIndex]
    ON [iPerform].[SkipTrades]([TradeId] ASC);


GO
PRINT N'Creating [iPerform].[QuestionType]...';


GO
CREATE TABLE [iPerform].[QuestionType] (
    [Id]   TINYINT       NOT NULL,
    [Name] NVARCHAR (50) COLLATE Latin1_General_CI_AS NULL,
    CONSTRAINT [PK_QuestionType] PRIMARY KEY CLUSTERED ([Id] ASC)
);


GO
PRINT N'Creating [iPerform].[QuestionGroup]...';


GO
CREATE TABLE [iPerform].[QuestionGroup] (
    [Id]             INT            IDENTITY (1, 1) NOT NULL,
    [Name]           NVARCHAR (100) COLLATE Latin1_General_CI_AS NULL,
    [UrlName]        NVARCHAR (100) COLLATE Latin1_General_CI_AS NULL,
    [ParentGroupId]  INT            NULL,
    [UserId]         INT            NULL,
    [AddedDt]        DATETIME       NULL,
    [TypeId]         TINYINT        NULL,
    [QuestionTypeId] TINYINT        NULL,
    [Active]         BIT            NULL,
    CONSTRAINT [PK_QuestionGroup] PRIMARY KEY CLUSTERED ([Id] ASC)
);


GO
PRINT N'Creating [iPerform].[Question]...';


GO
CREATE TABLE [iPerform].[Question] (
    [Id]               INT            IDENTITY (1, 1) NOT NULL,
    [Name]             NVARCHAR (500) COLLATE Latin1_General_CI_AS NULL,
    [Description]      NVARCHAR (MAX) COLLATE Latin1_General_CI_AS NULL,
    [TypeId]           TINYINT        NULL,
    [QuestionGroupId]  INT            NOT NULL,
    [UserId]           INT            NULL,
    [DefaultValue]     NVARCHAR (MAX) COLLATE Latin1_General_CI_AS NULL,
    [AddedDt]          DATETIME       NULL,
    [MinLabel]         NVARCHAR (150) COLLATE Latin1_General_CI_AS NULL,
    [MaxLabel]         NVARCHAR (150) COLLATE Latin1_General_CI_AS NULL,
    [Active]           BIT            NULL,
    [MinValue]         FLOAT (53)     NULL,
    [MaxValue]         FLOAT (53)     NULL,
    [SliderWeight]     INT            NULL,
    [QuestionParentId] INT            NULL,
    [SliderStep]       FLOAT (53)     NULL,
    [IsPercent]        BIT            NULL,
    CONSTRAINT [PK_Question] PRIMARY KEY CLUSTERED ([Id] ASC)
);


GO
PRINT N'Creating [iPerform].[PredictionAssistantFilter]...';


GO
CREATE TABLE [iPerform].[PredictionAssistantFilter] (
    [Id]           INT            IDENTITY (1, 1) NOT NULL,
    [UserId]       INT            NULL,
    [FundId]       INT            NULL,
    [BookId]       INT            NULL,
    [Country]      NVARCHAR (100) COLLATE Latin1_General_CI_AS NULL,
    [Sector]       NVARCHAR (100) COLLATE Latin1_General_CI_AS NULL,
    [Side]         SMALLINT       NULL,
    [SiAge]        BIT            NULL,
    [SiSide]       BIT            NULL,
    [SiMarketCap]  BIT            NULL,
    [SiEntryStyle] BIT            NULL,
    [SiStrategy]   BIT            NULL,
    [SiRoC]        BIT            NULL,
    [StrategyName] NVARCHAR (100) COLLATE Latin1_General_CI_AS NULL,
    CONSTRAINT [PK__Predicti__3214EC077775B2CE] PRIMARY KEY CLUSTERED ([Id] ASC),
    CONSTRAINT [UQ__Predicti__1788CC4D7A521F79] UNIQUE NONCLUSTERED ([UserId] ASC)
);


GO
PRINT N'Creating [iPerform].[PossibleAnswer]...';


GO
CREATE TABLE [iPerform].[PossibleAnswer] (
    [Id]                     INT            IDENTITY (1, 1) NOT NULL,
    [Name]                   NVARCHAR (500) COLLATE Latin1_General_CI_AS NULL,
    [QuestionId]             INT            NOT NULL,
    [ActiveStatus]           BIT            NULL,
    [PossibleAnswerParentId] INT            NULL,
    CONSTRAINT [PK_PossibleAnswer] PRIMARY KEY CLUSTERED ([Id] ASC)
);


GO
PRINT N'Creating [iPerform].[Journal]...';


GO
CREATE TABLE [iPerform].[Journal] (
    [QuestionGroupAnswerId] INT NULL,
    [SecurityId]            INT NULL,
    [Active]                BIT NULL,
    [UserId]                INT NULL
);


GO
PRINT N'Creating [iPerform].[AnswersGridState]...';


GO
CREATE TABLE [iPerform].[AnswersGridState] (
    [Id]           UNIQUEIDENTIFIER NOT NULL,
    [UserId]       INT              NOT NULL,
    [GridSettings] NVARCHAR (MAX)   COLLATE Latin1_General_CI_AS NOT NULL
);


GO
PRINT N'Creating [iPerform].[AnalysisGroup2User]...';


GO
CREATE TABLE [iPerform].[AnalysisGroup2User] (
    [AnalysisGroupId] INT NOT NULL,
    [UserId]          INT NOT NULL,
    CONSTRAINT [PK_AnalysisGroup2User] PRIMARY KEY CLUSTERED ([UserId] ASC, [AnalysisGroupId] ASC),
    CONSTRAINT [IX_AnalysisGroup2User] UNIQUE NONCLUSTERED ([AnalysisGroupId] ASC, [UserId] ASC)
);


GO
PRINT N'Creating [iPerform].[AnalysisGroup2FundBookEntity]...';


GO
CREATE TABLE [iPerform].[AnalysisGroup2FundBookEntity] (
    [AnalysisGroupId]  INT NOT NULL,
    [FundBookEntityId] INT NOT NULL,
    CONSTRAINT [PK_AnalysisGroup2FundBookEntity] PRIMARY KEY CLUSTERED ([AnalysisGroupId] ASC, [FundBookEntityId] ASC),
    CONSTRAINT [IX_AnalysisGroup2FundBookEntity] UNIQUE NONCLUSTERED ([AnalysisGroupId] ASC, [FundBookEntityId] ASC)
);


GO
PRINT N'Creating [iPerform].[AnalysisGroup]...';


GO
CREATE TABLE [iPerform].[AnalysisGroup] (
    [Id]   INT           IDENTITY (1, 1) NOT NULL,
    [Name] NVARCHAR (50) COLLATE Latin1_General_CI_AS NULL,
    CONSTRAINT [PK_iPerform.AnalysisGroup] PRIMARY KEY CLUSTERED ([Id] ASC),
    UNIQUE NONCLUSTERED ([Name] ASC)
);


GO
PRINT N'Creating [iPerform].[TradesToTemplateAnswerStartDate]...';


GO
CREATE TABLE [iPerform].[TradesToTemplateAnswerStartDate] (
    [FundBookEntityId] INT           NOT NULL,
    [SecurityId]       INT           NOT NULL,
    [Date]             SMALLDATETIME NULL,
    [LastTradeDate]    DATETIME2 (7) NOT NULL,
    CONSTRAINT [PK_iPerform.AnalysisStartDate] PRIMARY KEY CLUSTERED ([FundBookEntityId] ASC, [SecurityId] ASC)
);


GO
PRINT N'Creating [iPerform].[TemplateAnswer2Trade]...';


GO
CREATE TABLE [iPerform].[TemplateAnswer2Trade] (
    [TemplateAnswerId] INT NULL,
    [TradeId]          INT NULL
);


GO
PRINT N'Creating [iPerform].[Template2QuestionGroup]...';


GO
CREATE TABLE [iPerform].[Template2QuestionGroup] (
    [TemplateId]      INT      NOT NULL,
    [QuestionGroupId] INT      NOT NULL,
    [PositionX]       SMALLINT NULL,
    [PositionY]       SMALLINT NULL,
    [SizeX]           SMALLINT NULL,
    [SizeY]           SMALLINT NULL,
    [Active]          BIT      NULL,
    CONSTRAINT [PK_Template2QuestionGroup] PRIMARY KEY CLUSTERED ([QuestionGroupId] ASC, [TemplateId] ASC)
);


GO
PRINT N'Creating [iPerform].[Template2Question]...';


GO
CREATE TABLE [iPerform].[Template2Question] (
    [TemplateId]     INT      NOT NULL,
    [QuestionId]     INT      NOT NULL,
    [OrderNum]       SMALLINT NULL,
    [SliderWeight]   INT      NULL,
    [TextAreaHeight] INT      NULL,
    [IsAnswerable]   BIT      NULL,
    CONSTRAINT [PK_User2Question] PRIMARY KEY CLUSTERED ([TemplateId] ASC, [QuestionId] ASC)
);


GO
PRINT N'Creating [iPerform].[IdeaToTrade]...';


GO
CREATE TABLE [iPerform].[IdeaToTrade] (
    [IdeaId]           UNIQUEIDENTIFIER NOT NULL,
    [TradeId]          INT              NOT NULL,
    [LinkerEntityCode] SMALLINT         NOT NULL,
    [Timestamp]        DATETIME         NULL,
    CONSTRAINT [PK_IdeaToTrade] PRIMARY KEY CLUSTERED ([IdeaId] ASC, [TradeId] ASC)
);


GO
PRINT N'Creating [iPerform].[TemplateAnswer]...';


GO
CREATE TABLE [iPerform].[TemplateAnswer] (
    [Id]         INT      IDENTITY (1, 1) NOT NULL,
    [TemplateId] INT      NOT NULL,
    [UserId]     INT      NOT NULL,
    [AddedDt]    DATETIME NOT NULL,
    [CreatedOn]  DATETIME NOT NULL,
    CONSTRAINT [PK_QuestionTemplateAnswers] PRIMARY KEY CLUSTERED ([Id] ASC)
);


GO
PRINT N'Creating [iPerform].[Answer]...';


GO
CREATE TABLE [iPerform].[Answer] (
    [QuestionId]       INT            NOT NULL,
    [PossibleAnswerId] INT            NULL,
    [Value]            NVARCHAR (MAX) COLLATE Latin1_General_CI_AS NULL,
    [TemplateAnswerId] INT            NOT NULL,
    [AddedDt]          DATETIME       NOT NULL,
    [IsPercent]        BIT            NOT NULL,
    [Id]               INT            IDENTITY (1, 1) NOT NULL,
    CONSTRAINT [PK_Answer] PRIMARY KEY CLUSTERED ([Id] ASC)
);


GO
PRINT N'Creating [Membership].[User2Role]...';


GO
CREATE TABLE [Membership].[User2Role] (
    [UserId]  INT  NOT NULL,
    [RoleId]  INT  NOT NULL,
    [StartDt] DATE NULL,
    [EndDt]   DATE NULL,
    CONSTRAINT [PK__User2Rol__AF2760AD70D3A237] PRIMARY KEY CLUSTERED ([UserId] ASC, [RoleId] ASC)
);


GO
PRINT N'Creating [Membership].[User]...';


GO
CREATE TABLE [Membership].[User] (
    [Id]        INT            IDENTITY (1, 1) NOT NULL,
    [LoginName] NVARCHAR (150) COLLATE Latin1_General_CI_AS NOT NULL,
    [Password]  NVARCHAR (150) COLLATE Latin1_General_CI_AS NOT NULL,
    [Theme]     NVARCHAR (150) COLLATE Latin1_General_CI_AS NULL,
    [ContactId] INT            NULL,
    CONSTRAINT [PK_User] PRIMARY KEY CLUSTERED ([Id] ASC)
);


GO
PRINT N'Creating [Membership].[Role]...';


GO
CREATE TABLE [Membership].[Role] (
    [Id]          INT            IDENTITY (1, 1) NOT NULL,
    [Title]       NVARCHAR (50)  COLLATE Latin1_General_CI_AS NOT NULL,
    [EntityId]    INT            NULL,
    [Description] NVARCHAR (250) COLLATE Latin1_General_CI_AS NULL,
    CONSTRAINT [PK_Role] PRIMARY KEY CLUSTERED ([Id] ASC)
);


GO
PRINT N'Creating [Membership].[Entity]...';


GO
CREATE TABLE [Membership].[Entity] (
    [Id]   INT            IDENTITY (1, 1) NOT NULL,
    [Name] NVARCHAR (150) COLLATE Latin1_General_CI_AS NULL,
    CONSTRAINT [PK_Membership.Entity] PRIMARY KEY CLUSTERED ([Id] ASC)
);


GO
PRINT N'Creating [Membership].[Contact]...';


GO
CREATE TABLE [Membership].[Contact] (
    [Id]        INT            IDENTITY (1, 1) NOT NULL,
    [Title]     NVARCHAR (20)  COLLATE Latin1_General_CI_AS NULL,
    [FirstName] NVARCHAR (150) COLLATE Latin1_General_CI_AS NULL,
    [LastName]  NVARCHAR (150) COLLATE Latin1_General_CI_AS NULL,
    [Email]     NVARCHAR (150) COLLATE Latin1_General_CI_AS NULL,
    [Mobile]    NVARCHAR (100) COLLATE Latin1_General_CI_AS NULL,
    [Tel]       NVARCHAR (100) COLLATE Latin1_General_CI_AS NULL,
    [FullName]  AS             (RTRIM(LTRIM(ISNULL(Title + ' ', '') + ISNULL(FirstName + ' ', '') + ISNULL(LastName, '')))),
    CONSTRAINT [PK_Contact] PRIMARY KEY CLUSTERED ([Id] ASC)
);


GO
PRINT N'Creating [Reports].[SuccessPayoffTimeSeries]...';


GO
CREATE TABLE [Reports].[SuccessPayoffTimeSeries] (
    [FundBookEntityId] INT        NOT NULL,
    [Date]             DATE       NOT NULL,
    [SuccessRate]      FLOAT (53) NOT NULL,
    [PayoffRate]       FLOAT (53) NOT NULL
);


GO
PRINT N'Creating [Reports].[UserActionDetails]...';


GO
CREATE TABLE [Reports].[UserActionDetails] (
    [Id]           INT            IDENTITY (1, 1) NOT NULL,
    [UserActionId] INT            NOT NULL,
    [PropertyName] NVARCHAR (50)  NOT NULL,
    [PropertyType] NVARCHAR (50)  NOT NULL,
    [Value]        NVARCHAR (300) NULL,
    CONSTRAINT [PK_UUserActionDetails] PRIMARY KEY CLUSTERED ([Id] ASC)
);


GO
PRINT N'Creating [Reports].[UserAction]...';


GO
CREATE TABLE [Reports].[UserAction] (
    [Id]         INT           IDENTITY (1, 1) NOT NULL,
    [UserId]     INT           NOT NULL,
    [ActionName] NVARCHAR (50) NOT NULL,
    [DateTime]   DATETIME      NOT NULL,
    CONSTRAINT [PK_UserAction] PRIMARY KEY CLUSTERED ([Id] ASC)
);


GO
PRINT N'Creating [Reports].[Tracers]...';


GO
CREATE TABLE [Reports].[Tracers] (
    [InvestmentId] INT             NOT NULL,
    [TracerData]   VARBINARY (MAX) NULL,
    PRIMARY KEY CLUSTERED ([InvestmentId] ASC)
);


GO
PRINT N'Creating [Reports].[NormalisedProfileData]...';


GO
CREATE TABLE [Reports].[NormalisedProfileData] (
    [Id]           INT              IDENTITY (1, 1) NOT NULL,
    [InvestmentId] INT              NOT NULL,
    [IdeaId]       UNIQUEIDENTIFIER NOT NULL,
    [EntryLevel]   FLOAT (53)       NOT NULL,
    [ExitLevel]    FLOAT (53)       NOT NULL,
    PRIMARY KEY CLUSTERED ([Id] ASC)
);


GO
PRINT N'Creating [Reports].[SuccessPayoffFundBook2Investment]...';


GO
CREATE TABLE [Reports].[SuccessPayoffFundBook2Investment] (
    [FundBookEntityId] INT NOT NULL,
    [InvestmentId]     INT NOT NULL
);


GO
PRINT N'Creating [Settings].[Parameters]...';


GO
CREATE TABLE [Settings].[Parameters] (
    [Id]      INT             IDENTITY (1, 1) NOT NULL,
    [Created] DATETIME2 (0)   NOT NULL,
    [UserId]  INT             NULL,
    [Type]    NVARCHAR (1024) COLLATE Latin1_General_CI_AS NULL,
    [Value]   NVARCHAR (MAX)  COLLATE Latin1_General_CI_AS NULL,
    CONSTRAINT [PK_iComply.CalculationParameterSet] PRIMARY KEY CLUSTERED ([Id] ASC)
);


GO
PRINT N'Creating [Settings].[FileUpload]...';


GO
CREATE TABLE [Settings].[FileUpload] (
    [Id]               INT            IDENTITY (1, 1) NOT NULL,
    [OriginalFileName] NVARCHAR (512) NOT NULL,
    [Path]             NVARCHAR (512) NOT NULL,
    [Created]          DATETIME2 (7)  NOT NULL,
    [ContentType]      NVARCHAR (512) NOT NULL,
    CONSTRAINT [PK_FileUpload] PRIMARY KEY CLUSTERED ([Id] ASC)
);


GO
PRINT N'Creating [System].[RefactorLog]...';


GO
CREATE TABLE [System].[RefactorLog] (
    [Id]          INT            IDENTITY (1, 1) NOT NULL,
    [file-name]   VARCHAR (50)   NOT NULL,
    [runing-date] DATETIME       NOT NULL,
    [description] VARCHAR (1000) NOT NULL,
    PRIMARY KEY CLUSTERED ([Id] ASC)
);


GO
PRINT N'Creating [TradingData].[BenchmarkList]...';


GO
CREATE TABLE [TradingData].[BenchmarkList] (
    [Id]         INT            IDENTITY (1, 1) NOT NULL,
    [Region]     NVARCHAR (50)  COLLATE Latin1_General_CI_AS NOT NULL,
    [Sector]     NVARCHAR (500) COLLATE Latin1_General_CI_AS NOT NULL,
    [Ticker]     NVARCHAR (500) COLLATE Latin1_General_CI_AS NOT NULL,
    [Name]       NVARCHAR (500) COLLATE Latin1_General_CI_AS NOT NULL,
    [SecurityId] INT            NOT NULL,
    CONSTRAINT [PK_BenchmarkList] PRIMARY KEY CLUSTERED ([Id] ASC)
);


GO
PRINT N'Creating [TradingData].[EconomicSector]...';


GO
CREATE TABLE [TradingData].[EconomicSector] (
    [Id]                        INT           IDENTITY (1, 1) NOT NULL,
    [EconomicSectorId]          INT           NOT NULL,
    [EconomicSectorDescription] VARCHAR (100) NULL,
    [BusinesSectorId]           INT           NULL,
    [BusinesSectorDescription]  VARCHAR (100) NULL,
    [IndustryGroupId]           INT           NULL,
    [IndustryGroupDescription]  VARCHAR (100) NULL,
    PRIMARY KEY CLUSTERED ([Id] ASC)
);


GO
PRINT N'Creating [TradingData].[EconomicSector].[IX_EconomicSector_AllIds]...';


GO
CREATE NONCLUSTERED INDEX [IX_EconomicSector_AllIds]
    ON [TradingData].[EconomicSector]([EconomicSectorId] ASC, [BusinesSectorId] ASC, [IndustryGroupId] ASC);


GO
PRINT N'Creating [TradingData].[Country]...';


GO
CREATE TABLE [TradingData].[Country] (
    [Id]                 INT           IDENTITY (1, 1) NOT NULL,
    [CountryCode]        NVARCHAR (2)  NOT NULL,
    [CountryDescription] NVARCHAR (50) NOT NULL,
    [Region]             NVARCHAR (50) NULL,
    CONSTRAINT [PK_Country] PRIMARY KEY CLUSTERED ([Id] ASC)
);


GO
PRINT N'Creating [TradingData].[Country].[IX_Country_CountryCode]...';


GO
CREATE NONCLUSTERED INDEX [IX_Country_CountryCode]
    ON [TradingData].[Country]([Id] ASC, [CountryCode] ASC);


GO
PRINT N'Creating [TradingData].[TradeConsistencyCheck]...';


GO
CREATE TABLE [TradingData].[TradeConsistencyCheck] (
    [FundBookEntityId]  INT             NOT NULL,
    [SecurityId]        INT             NOT NULL,
    [Date]              DATETIME        NOT NULL,
    [TodayPosition]     DECIMAL (18, 4) NOT NULL,
    [YesterdayPosition] DECIMAL (18, 4) NOT NULL,
    [TradeQuantity]     DECIMAL (18, 4) NOT NULL,
    CONSTRAINT [PK_TradeConsistencyCheck] PRIMARY KEY CLUSTERED ([FundBookEntityId] ASC, [SecurityId] ASC, [Date] ASC)
);


GO
PRINT N'Creating [TradingData].[FundBookTradeLastUpdate]...';


GO
CREATE TABLE [TradingData].[FundBookTradeLastUpdate] (
    [FundBookEntityId] INT  NOT NULL,
    [TradeDate]        DATE NOT NULL,
    PRIMARY KEY CLUSTERED ([FundBookEntityId] ASC)
);


GO
PRINT N'Creating [TradingData].[TradeUpdate]...';


GO
CREATE TABLE [TradingData].[TradeUpdate] (
    [Id]      INT IDENTITY (1, 1) NOT NULL,
    [TradeId] INT NOT NULL,
    CONSTRAINT [PK_TradingData.TradeUpdate] PRIMARY KEY CLUSTERED ([Id] ASC)
);


GO
PRINT N'Creating [TradingData].[Strategy]...';


GO
CREATE TABLE [TradingData].[Strategy] (
    [Id]   INT         IDENTITY (1, 1) NOT NULL,
    [Name] NCHAR (200) COLLATE Latin1_General_CI_AS NOT NULL,
    CONSTRAINT [PK_Strategy] PRIMARY KEY CLUSTERED ([Id] ASC)
);


GO
PRINT N'Creating [TradingData].[SecurityClass]...';


GO
CREATE TABLE [TradingData].[SecurityClass] (
    [Id]            INT            IDENTITY (1, 1) NOT NULL,
    [Name]          NVARCHAR (500) COLLATE Latin1_General_CI_AS NOT NULL,
    [EnumName]      NVARCHAR (500) COLLATE Latin1_General_CI_AS NOT NULL,
    [Include]       BIT            NOT NULL,
    [IncludeInCalc] BIT            NOT NULL,
    CONSTRAINT [PK_Security.Class] PRIMARY KEY CLUSTERED ([Id] ASC)
);


GO
PRINT N'Creating [TradingData].[OriginalPosition]...';


GO
CREATE TABLE [TradingData].[OriginalPosition] (
    [SecurityId]       INT             NOT NULL,
    [FundBookEntityId] INT             NOT NULL,
    [Date]             DATE            NOT NULL,
    [CloseQuantity]    DECIMAL (18, 4) NULL,
    [CloseExposure]    DECIMAL (18, 4) NULL,
    [PositionPL]       DECIMAL (18, 4) NULL,
    [PositionFxPL]     DECIMAL (18, 4) NULL,
    [TradePL]          DECIMAL (18, 4) NULL,
    [TradeFxPL]        DECIMAL (18, 4) NULL,
    CONSTRAINT [PK_OriginalPosition] PRIMARY KEY CLUSTERED ([FundBookEntityId] ASC, [SecurityId] ASC, [Date] ASC)
);


GO
PRINT N'Creating [TradingData].[Manager]...';


GO
CREATE TABLE [TradingData].[Manager] (
    [Id]   INT           IDENTITY (1, 1) NOT NULL,
    [Name] NVARCHAR (50) COLLATE Latin1_General_CI_AS NULL,
    CONSTRAINT [PK_Manager] PRIMARY KEY CLUSTERED ([Id] ASC)
);


GO
PRINT N'Creating [TradingData].[FxSerie]...';


GO
CREATE TABLE [TradingData].[FxSerie] (
    [CurrencyId]  INT              NOT NULL,
    [Date]        DATE             NOT NULL,
    [FxRateInUsd] DECIMAL (18, 10) NOT NULL,
    CONSTRAINT [PK_FxSerie] PRIMARY KEY CLUSTERED ([CurrencyId] ASC, [Date] ASC)
);


GO
PRINT N'Creating [TradingData].[FundBookEntitySerie]...';


GO
CREATE TABLE [TradingData].[FundBookEntitySerie] (
    [FundBookEntityId] INT             NOT NULL,
    [Date]             DATE            NOT NULL,
    [OpenCapital]      DECIMAL (18, 4) NULL,
    [CloseCapital]     DECIMAL (18, 4) NULL,
    CONSTRAINT [PK_FundBookEntitySerie] PRIMARY KEY CLUSTERED ([FundBookEntityId] ASC, [Date] ASC)
);


GO
PRINT N'Creating [TradingData].[FundBookEntity]...';


GO
CREATE TABLE [TradingData].[FundBookEntity] (
    [Id]         INT            IDENTITY (1, 1) NOT NULL,
    [OriginalId] NVARCHAR (50)  NOT NULL,
    [ParentId]   INT            NULL,
    [Name]       NVARCHAR (100) COLLATE Latin1_General_CI_AS NOT NULL,
    [CurrencyId] INT            NOT NULL,
    [IsActive]   BIT            NOT NULL,
    CONSTRAINT [PK_Book] PRIMARY KEY CLUSTERED ([Id] ASC)
);


GO
PRINT N'Creating [TradingData].[ExcessTimeSerie]...';


GO
CREATE TABLE [TradingData].[ExcessTimeSerie] (
    [SecurityId]           INT              NOT NULL,
    [BenchmarkId]          INT              NOT NULL,
    [Date]                 DATE             NOT NULL,
    [ExcessReturn]         AS               ([SecurityReturn] - [BenchmarkReturn]),
    [Sigma]                FLOAT (53)       NOT NULL,
    [SigmaMove]            FLOAT (53)       NOT NULL,
    [SecurityReturn]       DECIMAL (18, 10) NOT NULL,
    [BenchmarkReturn]      DECIMAL (18, 10) NOT NULL,
    [PreviousUnitPosition] DECIMAL (28, 10) NOT NULL,
    CONSTRAINT [PK_TradingData.ExcessTimeSerie] PRIMARY KEY CLUSTERED ([SecurityId] ASC, [BenchmarkId] ASC, [Date] ASC)
);


GO
PRINT N'Creating [TradingData].[Currency]...';


GO
CREATE TABLE [TradingData].[Currency] (
    [Id]      INT            IDENTITY (1, 1) NOT NULL,
    [Name]    NVARCHAR (100) COLLATE Latin1_General_CI_AS NULL,
    [ISOCode] NCHAR (3)      COLLATE Latin1_General_CI_AS NULL,
    [Symbol]  NCHAR (2)      COLLATE Latin1_General_CI_AS NULL,
    CONSTRAINT [PK_Currency] PRIMARY KEY CLUSTERED ([Id] ASC)
);


GO
PRINT N'Creating [TradingData].[CorporateEvent]...';


GO
CREATE TABLE [TradingData].[CorporateEvent] (
    [CorporateActionId]       INT           NOT NULL,
    [SecurityId]              INT           NOT NULL,
    [Date]                    DATE          NOT NULL,
    [CorporateActionType]     VARCHAR (100) COLLATE Latin1_General_CI_AS NOT NULL,
    [DeclarationDate]         DATE          NOT NULL,
    [ExDate]                  DATE          NOT NULL,
    [PayDate]                 DATE          NULL,
    [RecordDate]              DATE          NULL,
    [AdjustmentFactor]        DECIMAL (18)  NULL,
    [GrossDividend]           MONEY         NULL,
    [CurrentDividendCurrency] VARCHAR (20)  COLLATE Latin1_General_CI_AS NULL,
    [Frequency]               VARCHAR (30)  COLLATE Latin1_General_CI_AS NULL,
    [Id]                      INT           IDENTITY (1, 1) NOT NULL,
    CONSTRAINT [PK_CorporateEvent_1] PRIMARY KEY CLUSTERED ([Id] ASC)
);


GO
PRINT N'Creating [TradingData].[BookSecurityToBenchmark]...';


GO
CREATE TABLE [TradingData].[BookSecurityToBenchmark] (
    [SecurityId]       INT NOT NULL,
    [FundBookEntityId] INT NOT NULL,
    [BenchmarkId]      INT NOT NULL,
    CONSTRAINT [PK_BookSecurityToBenchmark] PRIMARY KEY CLUSTERED ([FundBookEntityId] ASC, [SecurityId] ASC)
);


GO
PRINT N'Creating [TradingData].[BookBenchmarkType]...';


GO
CREATE TABLE [TradingData].[BookBenchmarkType] (
    [FundBookEntityId] INT           NOT NULL,
    [BenchmarkType]    NVARCHAR (50) COLLATE Latin1_General_CI_AS NOT NULL,
    CONSTRAINT [PK_BookBenchmarkType] PRIMARY KEY CLUSTERED ([FundBookEntityId] ASC)
);


GO
PRINT N'Creating [TradingData].[Trade]...';


GO
CREATE TABLE [TradingData].[Trade] (
    [Id]               INT              IDENTITY (1, 1) NOT NULL,
    [Quantity]         MONEY            NOT NULL,
    [Price]            MONEY            NOT NULL,
    [SecurityId]       INT              NOT NULL,
    [FundBookEntityId] INT              NOT NULL,
    [Date]             DATETIME2 (7)    NOT NULL,
    [TraderId]         INT              NOT NULL,
    [StrategyId]       INT              NOT NULL,
    [CurrencyId]       INT              NOT NULL,
    [FxRate]           DECIMAL (18, 10) NULL,
    [OriginalId]       NVARCHAR (40)    NULL,
    [AdditionalData]   NVARCHAR (MAX)   NULL,
    CONSTRAINT [PK_Trade] PRIMARY KEY CLUSTERED ([Id] ASC)
);


GO
PRINT N'Creating [TradingData].[Trade].[IX_Trade_BookSecurity]...';


GO
CREATE NONCLUSTERED INDEX [IX_Trade_BookSecurity]
    ON [TradingData].[Trade]([SecurityId] ASC, [FundBookEntityId] ASC, [Date] ASC);


GO
PRINT N'Creating [TradingData].[Trade].[IX_Trade_OriginalId]...';


GO
CREATE UNIQUE NONCLUSTERED INDEX [IX_Trade_OriginalId]
    ON [TradingData].[Trade]([OriginalId] ASC)
    INCLUDE([Id]);


GO
PRINT N'Creating [TradingData].[TimeSerie]...';


GO
CREATE TABLE [TradingData].[TimeSerie] (
    [SecurityId]           INT              NOT NULL,
    [Date]                 DATE             NOT NULL,
    [Open]                 DECIMAL (18, 4)  NULL,
    [AdjustedOpen]         DECIMAL (18, 4)  NULL,
    [Close]                DECIMAL (18, 4)  NULL,
    [AdjustedClose]        DECIMAL (18, 4)  NULL,
    [High]                 DECIMAL (18, 4)  NULL,
    [AdjustedHigh]         DECIMAL (18, 4)  NULL,
    [Low]                  DECIMAL (18, 4)  NULL,
    [AdjustedLow]          DECIMAL (18, 4)  NULL,
    [StockReturn]          DECIMAL (28, 10) NULL,
    [Bbg20DayMedianVolume] DECIMAL (18, 4)  NULL,
    [Bbg90DayVolatility]   DECIMAL (18, 4)  NULL,
    [DayVolume]            DECIMAL (18, 4)  NULL,
    [AdjustedDayVolume]    DECIMAL (18, 4)  NULL,
    [PriceToBook]          DECIMAL (18, 4)  NULL,
    [PriceToCashflow]      DECIMAL (18, 4)  NULL,
    [PriceToEarnings]      DECIMAL (18, 4)  NULL,
    [DividendYield]        DECIMAL (18, 4)  NULL,
    [MarketCap]            DECIMAL (28)     NULL,
    CONSTRAINT [PK_TimeSerie] PRIMARY KEY CLUSTERED ([SecurityId] ASC, [Date] ASC)
);


GO
PRINT N'Creating [TradingData].[TimeSerie].[IX_TimeSerie_SecurityId]...';


GO
CREATE NONCLUSTERED INDEX [IX_TimeSerie_SecurityId]
    ON [TradingData].[TimeSerie]([SecurityId] ASC)
    INCLUDE([Date], [Close], [MarketCap]);


GO
PRINT N'Creating [TradingData].[Security]...';


GO
CREATE TABLE [TradingData].[Security] (
    [Id]                  INT            IDENTITY (1, 1) NOT NULL,
    [Isin]                VARCHAR (20)   NULL,
    [Ric]                 VARCHAR (20)   NULL,
    [Mic]                 VARCHAR (20)   NULL,
    [Sedol]               VARCHAR (20)   NULL,
    [CustomIdentificator] VARCHAR (20)   NULL,
    [Name]                NVARCHAR (MAX) NULL,
    [LongName]            NVARCHAR (MAX) NULL,
    [ClassId]             INT            NULL,
    [CurrencyId]          INT            NULL,
    [EconomicSectorId]    INT            NULL,
    [CountryId]           INT            NOT NULL,
    [ParentId]            INT            NULL,
    [ParentIdentify]      VARCHAR (20)   NULL,
    [ParentIdentifyType]  VARCHAR (20)   NULL,
    [Ticker]              NVARCHAR (500) NULL,
    [Type]                TINYINT        NULL,
    CONSTRAINT [PK_Security] PRIMARY KEY CLUSTERED ([Id] ASC)
);


GO
PRINT N'Creating [TradingData].[Security].[IX_Security_Isin]...';


GO
CREATE NONCLUSTERED INDEX [IX_Security_Isin]
    ON [TradingData].[Security]([Isin] ASC);


GO
PRINT N'Creating [TradingData].[Security].[IX_Security_Ric]...';


GO
CREATE NONCLUSTERED INDEX [IX_Security_Ric]
    ON [TradingData].[Security]([Ric] ASC);


GO
PRINT N'Creating Default Constraint on [Analytics].[InvestmentSerie]....';


GO
ALTER TABLE [Analytics].[InvestmentSerie]
    ADD DEFAULT 0 FOR [AgeByTimeSeries];


GO
PRINT N'Creating Default Constraint on [iComply].[ComplianceEvent]....';


GO
ALTER TABLE [iComply].[ComplianceEvent]
    ADD DEFAULT 0 FOR [Status];


GO
PRINT N'Creating Default Constraint on [iComply].[ComplianceEvent]....';


GO
ALTER TABLE [iComply].[ComplianceEvent]
    ADD DEFAULT 0 FOR [SigmaMove];


GO
PRINT N'Creating Default Constraint on [iComply].[SuspiciousActivity]....';


GO
ALTER TABLE [iComply].[SuspiciousActivity]
    ADD DEFAULT 1 FOR [IsActive];


GO
PRINT N'Creating Default Constraint on [iComply].[SuspiciousActivity]....';


GO
ALTER TABLE [iComply].[SuspiciousActivity]
    ADD DEFAULT 0 FOR [ExcessReturn];


GO
PRINT N'Creating DF_TradeUnderSurveillance__Weight...';


GO
ALTER TABLE [iComply].[TradeUnderSurveillance]
    ADD CONSTRAINT [DF_TradeUnderSurveillance__Weight] DEFAULT ((0)) FOR [WeightSum];


GO
PRINT N'Creating DF__TradesToT__LastT__2215F810...';


GO
ALTER TABLE [iPerform].[TradesToTemplateAnswerStartDate]
    ADD CONSTRAINT [DF__TradesToT__LastT__2215F810] DEFAULT ('1753-01-01') FOR [LastTradeDate];


GO
PRINT N'Creating Default Constraint on [iPerform].[TemplateAnswer]....';


GO
ALTER TABLE [iPerform].[TemplateAnswer]
    ADD DEFAULT '1990-08-18' FOR [CreatedOn];


GO
PRINT N'Creating DF_iComply.Settings_Created...';


GO
ALTER TABLE [Settings].[Parameters]
    ADD CONSTRAINT [DF_iComply.Settings_Created] DEFAULT (sysdatetime()) FOR [Created];


GO
PRINT N'Creating DF_SecurityClass_Include...';


GO
ALTER TABLE [TradingData].[SecurityClass]
    ADD CONSTRAINT [DF_SecurityClass_Include] DEFAULT ((1)) FOR [Include];


GO
PRINT N'Creating DF__SecurityC__Inclu__68DD7AB4...';


GO
ALTER TABLE [TradingData].[SecurityClass]
    ADD CONSTRAINT [DF__SecurityC__Inclu__68DD7AB4] DEFAULT ((0)) FOR [IncludeInCalc];


GO
PRINT N'Creating DF_FundBookEntity_IsActive...';


GO
ALTER TABLE [TradingData].[FundBookEntity]
    ADD CONSTRAINT [DF_FundBookEntity_IsActive] DEFAULT ((1)) FOR [IsActive];


GO
PRINT N'Creating DF_TradingData.ExcessTimeSerie_SecurityReturn...';


GO
ALTER TABLE [TradingData].[ExcessTimeSerie]
    ADD CONSTRAINT [DF_TradingData.ExcessTimeSerie_SecurityReturn] DEFAULT ((0)) FOR [SecurityReturn];


GO
PRINT N'Creating Foreign Key on [Analytics].[IdeaToTrade]....';


GO
ALTER TABLE [Analytics].[IdeaToTrade] WITH NOCHECK
    ADD FOREIGN KEY ([IdeaId]) REFERENCES [Analytics].[Idea] ([Id]) ON DELETE CASCADE;


GO
PRINT N'Creating Foreign Key on [Analytics].[IdeaToSecurity]....';


GO
ALTER TABLE [Analytics].[IdeaToSecurity] WITH NOCHECK
    ADD FOREIGN KEY ([IdeaId]) REFERENCES [Analytics].[Idea] ([Id]) ON DELETE CASCADE;


GO
PRINT N'Creating FK_TradedQuantity_Investment...';


GO
ALTER TABLE [Analytics].[TradedQuantity] WITH NOCHECK
    ADD CONSTRAINT [FK_TradedQuantity_Investment] FOREIGN KEY ([InvestmentId]) REFERENCES [Analytics].[Investment] ([Id]) ON DELETE CASCADE;


GO
PRINT N'Creating FK_InvestmentSerie_Investment...';


GO
ALTER TABLE [Analytics].[InvestmentSerie] WITH NOCHECK
    ADD CONSTRAINT [FK_InvestmentSerie_Investment] FOREIGN KEY ([InvestmentId]) REFERENCES [Analytics].[Investment] ([Id]) ON DELETE CASCADE;


GO
PRINT N'Creating FK_InvestmentSerie_Security...';


GO
ALTER TABLE [Analytics].[InvestmentSerie] WITH NOCHECK
    ADD CONSTRAINT [FK_InvestmentSerie_Security] FOREIGN KEY ([SecurityId]) REFERENCES [TradingData].[Security] ([Id]);


GO
PRINT N'Creating FK_Investment_Idea...';


GO
ALTER TABLE [Analytics].[Investment] WITH NOCHECK
    ADD CONSTRAINT [FK_Investment_Idea] FOREIGN KEY ([IdeaId]) REFERENCES [Analytics].[Idea] ([Id]) ON DELETE CASCADE;


GO
PRINT N'Creating Foreign Key on [Analytics].[TracePoint]....';


GO
ALTER TABLE [Analytics].[TracePoint] WITH NOCHECK
    ADD FOREIGN KEY ([InvestmentId]) REFERENCES [Analytics].[Investment] ([Id]) ON DELETE CASCADE;


GO
PRINT N'Creating FK_Exposure_FundBookEntity...';


GO
ALTER TABLE [DataConsistency].[SuspiciousExposure] WITH NOCHECK
    ADD CONSTRAINT [FK_Exposure_FundBookEntity] FOREIGN KEY ([FundBookEntityId]) REFERENCES [TradingData].[FundBookEntity] ([Id]);


GO
PRINT N'Creating FK_Exposure_Security...';


GO
ALTER TABLE [DataConsistency].[SuspiciousExposure] WITH NOCHECK
    ADD CONSTRAINT [FK_Exposure_Security] FOREIGN KEY ([SecurityId]) REFERENCES [TradingData].[Security] ([Id]);


GO
PRINT N'Creating FK_Comments_CompliancePeriod...';


GO
ALTER TABLE [iComply].[Comments] WITH NOCHECK
    ADD CONSTRAINT [FK_Comments_CompliancePeriod] FOREIGN KEY ([PeriodId]) REFERENCES [iComply].[CompliancePeriod] ([Id]) ON DELETE CASCADE ON UPDATE CASCADE;


GO
PRINT N'Creating FK_Comments_Comments...';


GO
ALTER TABLE [iComply].[Comments] WITH NOCHECK
    ADD CONSTRAINT [FK_Comments_Comments] FOREIGN KEY ([ParentId]) REFERENCES [iComply].[Comments] ([Id]);


GO
PRINT N'Creating FK_Comments_User...';


GO
ALTER TABLE [iComply].[Comments] WITH NOCHECK
    ADD CONSTRAINT [FK_Comments_User] FOREIGN KEY ([Author]) REFERENCES [Membership].[User] ([Id]);


GO
PRINT N'Creating FK_IncludedEventSecurityClass_SecurityClass...';


GO
ALTER TABLE [iComply].[IncludedEventSecurityClass] WITH NOCHECK
    ADD CONSTRAINT [FK_IncludedEventSecurityClass_SecurityClass] FOREIGN KEY ([SecurityClassId]) REFERENCES [TradingData].[SecurityClass] ([Id]) ON DELETE CASCADE ON UPDATE CASCADE;


GO
PRINT N'Creating FK_ComplianceEvent_Security...';


GO
ALTER TABLE [iComply].[ComplianceEvent] WITH NOCHECK
    ADD CONSTRAINT [FK_ComplianceEvent_Security] FOREIGN KEY ([SecurityId]) REFERENCES [TradingData].[Security] ([Id]) ON DELETE CASCADE ON UPDATE CASCADE;


GO
PRINT N'Creating FK_CommentToFileUpload_Comment...';


GO
ALTER TABLE [iComply].[CommentToFileUpload] WITH NOCHECK
    ADD CONSTRAINT [FK_CommentToFileUpload_Comment] FOREIGN KEY ([CommentId]) REFERENCES [iComply].[Comments] ([Id]) ON DELETE CASCADE ON UPDATE CASCADE;


GO
PRINT N'Creating FK_CommentToFileUpload_FileUpload...';


GO
ALTER TABLE [iComply].[CommentToFileUpload] WITH NOCHECK
    ADD CONSTRAINT [FK_CommentToFileUpload_FileUpload] FOREIGN KEY ([FileUploadId]) REFERENCES [Settings].[FileUpload] ([Id]) ON DELETE CASCADE ON UPDATE CASCADE;


GO
PRINT N'Creating FK_SuspiciousActivityToComplianceEvent...';


GO
ALTER TABLE [iComply].[SuspiciousActivity] WITH NOCHECK
    ADD CONSTRAINT [FK_SuspiciousActivityToComplianceEvent] FOREIGN KEY ([EventId]) REFERENCES [iComply].[ComplianceEvent] ([Id]);


GO
PRINT N'Creating FK_SuspiciousActivityToFundBookEntityId...';


GO
ALTER TABLE [iComply].[SuspiciousActivity] WITH NOCHECK
    ADD CONSTRAINT [FK_SuspiciousActivityToFundBookEntityId] FOREIGN KEY ([FundBookEntityId]) REFERENCES [TradingData].[FundBookEntity] ([Id]);


GO
PRINT N'Creating FK_TradeUnderSurveillanceToSuspiciousActivity...';


GO
ALTER TABLE [iComply].[TradeUnderSurveillance] WITH NOCHECK
    ADD CONSTRAINT [FK_TradeUnderSurveillanceToSuspiciousActivity] FOREIGN KEY ([SuspiciousActivityId]) REFERENCES [iComply].[SuspiciousActivity] ([Id]) ON DELETE CASCADE ON UPDATE CASCADE;


GO
PRINT N'Creating FK_TradeUnderSurveillance_Trade...';


GO
ALTER TABLE [iComply].[TradeUnderSurveillance] WITH NOCHECK
    ADD CONSTRAINT [FK_TradeUnderSurveillance_Trade] FOREIGN KEY ([TradeId]) REFERENCES [TradingData].[Trade] ([Id]) ON DELETE CASCADE ON UPDATE CASCADE;


GO
PRINT N'Creating FK_TradeHeatItem_iComply.TradeUnderSurveillance...';


GO
ALTER TABLE [iComply].[TradeHeatItems] WITH NOCHECK
    ADD CONSTRAINT [FK_TradeHeatItem_iComply.TradeUnderSurveillance] FOREIGN KEY ([ComplianceTradeId]) REFERENCES [iComply].[TradeUnderSurveillance] ([Id]) ON DELETE CASCADE ON UPDATE CASCADE;


GO
PRINT N'Creating FK_CompliancePeriodToSuspiciousActivity_CompliancePeriod...';


GO
ALTER TABLE [iComply].[CompliancePeriodToSuspiciousActivity] WITH NOCHECK
    ADD CONSTRAINT [FK_CompliancePeriodToSuspiciousActivity_CompliancePeriod] FOREIGN KEY ([CompliancePeriodId]) REFERENCES [iComply].[CompliancePeriod] ([Id]) ON DELETE CASCADE ON UPDATE CASCADE;


GO
PRINT N'Creating FK_CompliancePeriodToSuspiciousActivity_SuspiciousActivity...';


GO
ALTER TABLE [iComply].[CompliancePeriodToSuspiciousActivity] WITH NOCHECK
    ADD CONSTRAINT [FK_CompliancePeriodToSuspiciousActivity_SuspiciousActivity] FOREIGN KEY ([SuspiciousActivityId]) REFERENCES [iComply].[SuspiciousActivity] ([Id]) ON DELETE CASCADE ON UPDATE CASCADE;


GO
PRINT N'Creating FK_CompliancePeriod_User...';


GO
ALTER TABLE [iComply].[CompliancePeriod] WITH NOCHECK
    ADD CONSTRAINT [FK_CompliancePeriod_User] FOREIGN KEY ([OwnerId]) REFERENCES [Membership].[User] ([Id]);


GO
PRINT N'Creating FK_Layout_User...';


GO
ALTER TABLE [iPerform].[Layout] WITH NOCHECK
    ADD CONSTRAINT [FK_Layout_User] FOREIGN KEY ([UserId]) REFERENCES [Membership].[User] ([Id]);


GO
PRINT N'Creating FK_Template2PossibleAnswer_PossibleAnswer...';


GO
ALTER TABLE [iPerform].[Template2PossibleAnswer] WITH NOCHECK
    ADD CONSTRAINT [FK_Template2PossibleAnswer_PossibleAnswer] FOREIGN KEY ([PossibleAnswerId]) REFERENCES [iPerform].[PossibleAnswer] ([Id]);


GO
PRINT N'Creating FK_Template2PossibleAnswer_Template...';


GO
ALTER TABLE [iPerform].[Template2PossibleAnswer] WITH NOCHECK
    ADD CONSTRAINT [FK_Template2PossibleAnswer_Template] FOREIGN KEY ([TemplateId]) REFERENCES [iPerform].[Template] ([Id]);


GO
PRINT N'Creating FK_Template_Template...';


GO
ALTER TABLE [iPerform].[Template] WITH NOCHECK
    ADD CONSTRAINT [FK_Template_Template] FOREIGN KEY ([ParentId]) REFERENCES [iPerform].[Template] ([Id]);


GO
PRINT N'Creating FK_IdeaToSecurity_Idea...';


GO
ALTER TABLE [iPerform].[IdeaToSecurity] WITH NOCHECK
    ADD CONSTRAINT [FK_IdeaToSecurity_Idea] FOREIGN KEY ([IdeaId]) REFERENCES [iPerform].[Idea] ([Id]) ON DELETE CASCADE;


GO
PRINT N'Creating FK_Question_QuestionGroup...';


GO
ALTER TABLE [iPerform].[Question] WITH NOCHECK
    ADD CONSTRAINT [FK_Question_QuestionGroup] FOREIGN KEY ([QuestionGroupId]) REFERENCES [iPerform].[QuestionGroup] ([Id]);


GO
PRINT N'Creating FK_Question_Question...';


GO
ALTER TABLE [iPerform].[Question] WITH NOCHECK
    ADD CONSTRAINT [FK_Question_Question] FOREIGN KEY ([QuestionParentId]) REFERENCES [iPerform].[Question] ([Id]);


GO
PRINT N'Creating FK_PossibleAnswer_Question...';


GO
ALTER TABLE [iPerform].[PossibleAnswer] WITH NOCHECK
    ADD CONSTRAINT [FK_PossibleAnswer_Question] FOREIGN KEY ([QuestionId]) REFERENCES [iPerform].[Question] ([Id]);


GO
PRINT N'Creating FK_PossibleAnswer_PossibleAnswer...';


GO
ALTER TABLE [iPerform].[PossibleAnswer] WITH NOCHECK
    ADD CONSTRAINT [FK_PossibleAnswer_PossibleAnswer] FOREIGN KEY ([PossibleAnswerParentId]) REFERENCES [iPerform].[PossibleAnswer] ([Id]);


GO
PRINT N'Creating FK_AnalysisGroup2User_AnalysisGroup...';


GO
ALTER TABLE [iPerform].[AnalysisGroup2User] WITH NOCHECK
    ADD CONSTRAINT [FK_AnalysisGroup2User_AnalysisGroup] FOREIGN KEY ([AnalysisGroupId]) REFERENCES [iPerform].[AnalysisGroup] ([Id]);


GO
PRINT N'Creating FK_AnalysisGroup2User_User...';


GO
ALTER TABLE [iPerform].[AnalysisGroup2User] WITH NOCHECK
    ADD CONSTRAINT [FK_AnalysisGroup2User_User] FOREIGN KEY ([UserId]) REFERENCES [Membership].[User] ([Id]);


GO
PRINT N'Creating FK_AnalysisGroup2FundBookEntity_AnalysisGroup...';


GO
ALTER TABLE [iPerform].[AnalysisGroup2FundBookEntity] WITH NOCHECK
    ADD CONSTRAINT [FK_AnalysisGroup2FundBookEntity_AnalysisGroup] FOREIGN KEY ([AnalysisGroupId]) REFERENCES [iPerform].[AnalysisGroup] ([Id]);


GO
PRINT N'Creating FK_AnalysisGroup2FundBookEntity_FundBookEntity...';


GO
ALTER TABLE [iPerform].[AnalysisGroup2FundBookEntity] WITH NOCHECK
    ADD CONSTRAINT [FK_AnalysisGroup2FundBookEntity_FundBookEntity] FOREIGN KEY ([FundBookEntityId]) REFERENCES [TradingData].[FundBookEntity] ([Id]);


GO
PRINT N'Creating FK_Template2QuestionGroup_Template...';


GO
ALTER TABLE [iPerform].[Template2QuestionGroup] WITH NOCHECK
    ADD CONSTRAINT [FK_Template2QuestionGroup_Template] FOREIGN KEY ([TemplateId]) REFERENCES [iPerform].[Template] ([Id]);


GO
PRINT N'Creating FK_Template2Question_Template...';


GO
ALTER TABLE [iPerform].[Template2Question] WITH NOCHECK
    ADD CONSTRAINT [FK_Template2Question_Template] FOREIGN KEY ([TemplateId]) REFERENCES [iPerform].[Template] ([Id]);


GO
PRINT N'Creating FK_Template2Question_Question...';


GO
ALTER TABLE [iPerform].[Template2Question] WITH NOCHECK
    ADD CONSTRAINT [FK_Template2Question_Question] FOREIGN KEY ([QuestionId]) REFERENCES [iPerform].[Question] ([Id]);


GO
PRINT N'Creating FK_IdeaToTrade_Idea...';


GO
ALTER TABLE [iPerform].[IdeaToTrade] WITH NOCHECK
    ADD CONSTRAINT [FK_IdeaToTrade_Idea] FOREIGN KEY ([IdeaId]) REFERENCES [iPerform].[Idea] ([Id]) ON DELETE CASCADE;


GO
PRINT N'Creating FK_TemplateAnswer_Template...';


GO
ALTER TABLE [iPerform].[TemplateAnswer] WITH NOCHECK
    ADD CONSTRAINT [FK_TemplateAnswer_Template] FOREIGN KEY ([TemplateId]) REFERENCES [iPerform].[Template] ([Id]);


GO
PRINT N'Creating FK_Answer_PossibleAnswer...';


GO
ALTER TABLE [iPerform].[Answer] WITH NOCHECK
    ADD CONSTRAINT [FK_Answer_PossibleAnswer] FOREIGN KEY ([PossibleAnswerId]) REFERENCES [iPerform].[PossibleAnswer] ([Id]);


GO
PRINT N'Creating FK_Answer_Question...';


GO
ALTER TABLE [iPerform].[Answer] WITH NOCHECK
    ADD CONSTRAINT [FK_Answer_Question] FOREIGN KEY ([QuestionId]) REFERENCES [iPerform].[Question] ([Id]);


GO
PRINT N'Creating FK_Answer_TemplateAnswer...';


GO
ALTER TABLE [iPerform].[Answer] WITH NOCHECK
    ADD CONSTRAINT [FK_Answer_TemplateAnswer] FOREIGN KEY ([TemplateAnswerId]) REFERENCES [iPerform].[TemplateAnswer] ([Id]);


GO
PRINT N'Creating FK_User2Role_Role...';


GO
ALTER TABLE [Membership].[User2Role] WITH NOCHECK
    ADD CONSTRAINT [FK_User2Role_Role] FOREIGN KEY ([RoleId]) REFERENCES [Membership].[Role] ([Id]);


GO
PRINT N'Creating FK_User2Role_User...';


GO
ALTER TABLE [Membership].[User2Role] WITH NOCHECK
    ADD CONSTRAINT [FK_User2Role_User] FOREIGN KEY ([UserId]) REFERENCES [Membership].[User] ([Id]);


GO
PRINT N'Creating FK_User_Contact...';


GO
ALTER TABLE [Membership].[User] WITH NOCHECK
    ADD CONSTRAINT [FK_User_Contact] FOREIGN KEY ([ContactId]) REFERENCES [Membership].[Contact] ([Id]);


GO
PRINT N'Creating FK_Role_Entity...';


GO
ALTER TABLE [Membership].[Role] WITH NOCHECK
    ADD CONSTRAINT [FK_Role_Entity] FOREIGN KEY ([EntityId]) REFERENCES [Membership].[Entity] ([Id]);


GO
PRINT N'Creating FK_UserActionDetails_UserAction...';


GO
ALTER TABLE [Reports].[UserActionDetails] WITH NOCHECK
    ADD CONSTRAINT [FK_UserActionDetails_UserAction] FOREIGN KEY ([UserActionId]) REFERENCES [Reports].[UserAction] ([Id]);


GO
PRINT N'Creating FK_UserAction_User...';


GO
ALTER TABLE [Reports].[UserAction] WITH NOCHECK
    ADD CONSTRAINT [FK_UserAction_User] FOREIGN KEY ([UserId]) REFERENCES [Membership].[User] ([Id]);


GO
PRINT N'Creating FK_BenchmarkList_Security...';


GO
ALTER TABLE [TradingData].[BenchmarkList] WITH NOCHECK
    ADD CONSTRAINT [FK_BenchmarkList_Security] FOREIGN KEY ([SecurityId]) REFERENCES [TradingData].[Security] ([Id]);


GO
PRINT N'Creating FK_TradeConsistencyCheck_Security...';


GO
ALTER TABLE [TradingData].[TradeConsistencyCheck] WITH NOCHECK
    ADD CONSTRAINT [FK_TradeConsistencyCheck_Security] FOREIGN KEY ([SecurityId]) REFERENCES [TradingData].[Security] ([Id]);


GO
PRINT N'Creating FK_TradeConsistencyCheck_FundbookEntity...';


GO
ALTER TABLE [TradingData].[TradeConsistencyCheck] WITH NOCHECK
    ADD CONSTRAINT [FK_TradeConsistencyCheck_FundbookEntity] FOREIGN KEY ([FundBookEntityId]) REFERENCES [TradingData].[FundBookEntity] ([Id]);


GO
PRINT N'Creating FK_FundBookTradeLastUpdate_FundBookEntityId...';


GO
ALTER TABLE [TradingData].[FundBookTradeLastUpdate] WITH NOCHECK
    ADD CONSTRAINT [FK_FundBookTradeLastUpdate_FundBookEntityId] FOREIGN KEY ([FundBookEntityId]) REFERENCES [TradingData].[FundBookEntity] ([Id]);


GO
PRINT N'Creating FK_OriginalPosition_Security...';


GO
ALTER TABLE [TradingData].[OriginalPosition] WITH NOCHECK
    ADD CONSTRAINT [FK_OriginalPosition_Security] FOREIGN KEY ([SecurityId]) REFERENCES [TradingData].[Security] ([Id]);


GO
PRINT N'Creating FK_OriginalPosition_FundBookEntity...';


GO
ALTER TABLE [TradingData].[OriginalPosition] WITH NOCHECK
    ADD CONSTRAINT [FK_OriginalPosition_FundBookEntity] FOREIGN KEY ([FundBookEntityId]) REFERENCES [TradingData].[FundBookEntity] ([Id]);


GO
PRINT N'Creating FK_FxSerie_Currency...';


GO
ALTER TABLE [TradingData].[FxSerie] WITH NOCHECK
    ADD CONSTRAINT [FK_FxSerie_Currency] FOREIGN KEY ([CurrencyId]) REFERENCES [TradingData].[Currency] ([Id]);


GO
PRINT N'Creating FK_FundBookEntitySerie_Fundbook...';


GO
ALTER TABLE [TradingData].[FundBookEntitySerie] WITH NOCHECK
    ADD CONSTRAINT [FK_FundBookEntitySerie_Fundbook] FOREIGN KEY ([FundBookEntityId]) REFERENCES [TradingData].[FundBookEntity] ([Id]);


GO
PRINT N'Creating FK_TradingData.Book_TradingData.Book...';


GO
ALTER TABLE [TradingData].[FundBookEntity] WITH NOCHECK
    ADD CONSTRAINT [FK_TradingData.Book_TradingData.Book] FOREIGN KEY ([ParentId]) REFERENCES [TradingData].[FundBookEntity] ([Id]);


GO
PRINT N'Creating FK_FundBookEntity_Currency...';


GO
ALTER TABLE [TradingData].[FundBookEntity] WITH NOCHECK
    ADD CONSTRAINT [FK_FundBookEntity_Currency] FOREIGN KEY ([CurrencyId]) REFERENCES [TradingData].[Currency] ([Id]);


GO
PRINT N'Creating FK_ExcessTimeSerie_Security...';


GO
ALTER TABLE [TradingData].[ExcessTimeSerie] WITH NOCHECK
    ADD CONSTRAINT [FK_ExcessTimeSerie_Security] FOREIGN KEY ([SecurityId]) REFERENCES [TradingData].[Security] ([Id]) ON DELETE CASCADE ON UPDATE CASCADE;


GO
PRINT N'Creating FK_ExcessTimeSerie_Benchmark...';


GO
ALTER TABLE [TradingData].[ExcessTimeSerie] WITH NOCHECK
    ADD CONSTRAINT [FK_ExcessTimeSerie_Benchmark] FOREIGN KEY ([BenchmarkId]) REFERENCES [TradingData].[Security] ([Id]);


GO
PRINT N'Creating FK_BookSecurityToBenchmark_FundBookEntity...';


GO
ALTER TABLE [TradingData].[BookSecurityToBenchmark] WITH NOCHECK
    ADD CONSTRAINT [FK_BookSecurityToBenchmark_FundBookEntity] FOREIGN KEY ([FundBookEntityId]) REFERENCES [TradingData].[FundBookEntity] ([Id]) ON DELETE CASCADE ON UPDATE CASCADE;


GO
PRINT N'Creating FK_BookSecurityToBenchmark_Security_Security...';


GO
ALTER TABLE [TradingData].[BookSecurityToBenchmark] WITH NOCHECK
    ADD CONSTRAINT [FK_BookSecurityToBenchmark_Security_Security] FOREIGN KEY ([SecurityId]) REFERENCES [TradingData].[Security] ([Id]) ON DELETE CASCADE ON UPDATE CASCADE;


GO
PRINT N'Creating FK_BookBenchmarkType_FundBookEntity...';


GO
ALTER TABLE [TradingData].[BookBenchmarkType] WITH NOCHECK
    ADD CONSTRAINT [FK_BookBenchmarkType_FundBookEntity] FOREIGN KEY ([FundBookEntityId]) REFERENCES [TradingData].[FundBookEntity] ([Id]) ON DELETE CASCADE ON UPDATE CASCADE;


GO
PRINT N'Creating FK_Trade_Currency...';


GO
ALTER TABLE [TradingData].[Trade] WITH NOCHECK
    ADD CONSTRAINT [FK_Trade_Currency] FOREIGN KEY ([CurrencyId]) REFERENCES [TradingData].[Currency] ([Id]);


GO
PRINT N'Creating FK_Trade_Security...';


GO
ALTER TABLE [TradingData].[Trade] WITH NOCHECK
    ADD CONSTRAINT [FK_Trade_Security] FOREIGN KEY ([SecurityId]) REFERENCES [TradingData].[Security] ([Id]);


GO
PRINT N'Creating FK_Trade_FundbookEntity...';


GO
ALTER TABLE [TradingData].[Trade] WITH NOCHECK
    ADD CONSTRAINT [FK_Trade_FundbookEntity] FOREIGN KEY ([FundBookEntityId]) REFERENCES [TradingData].[FundBookEntity] ([Id]);


GO
PRINT N'Creating FK_TimeSerie_Security...';


GO
ALTER TABLE [TradingData].[TimeSerie] WITH NOCHECK
    ADD CONSTRAINT [FK_TimeSerie_Security] FOREIGN KEY ([SecurityId]) REFERENCES [TradingData].[Security] ([Id]);


GO
PRINT N'Creating FK_Security_SecurityClass...';


GO
ALTER TABLE [TradingData].[Security] WITH NOCHECK
    ADD CONSTRAINT [FK_Security_SecurityClass] FOREIGN KEY ([ClassId]) REFERENCES [TradingData].[SecurityClass] ([Id]);


GO
PRINT N'Creating FK_Security_Country...';


GO
ALTER TABLE [TradingData].[Security] WITH NOCHECK
    ADD CONSTRAINT [FK_Security_Country] FOREIGN KEY ([CountryId]) REFERENCES [TradingData].[Country] ([Id]);


GO
PRINT N'Creating FK_Security_Currency...';


GO
ALTER TABLE [TradingData].[Security] WITH NOCHECK
    ADD CONSTRAINT [FK_Security_Currency] FOREIGN KEY ([CurrencyId]) REFERENCES [TradingData].[Currency] ([Id]);


GO
PRINT N'Creating FK_Security_EconomicSector...';


GO
ALTER TABLE [TradingData].[Security] WITH NOCHECK
    ADD CONSTRAINT [FK_Security_EconomicSector] FOREIGN KEY ([EconomicSectorId]) REFERENCES [TradingData].[EconomicSector] ([Id]);


GO
PRINT N'Creating [Analytics].[Trigger_UiaEntry_Timestamp]...';


GO

CREATE TRIGGER [Analytics].[Trigger_UiaEntry_Timestamp]
    ON [Analytics].[UiaEntry]
    FOR INSERT
    AS
    BEGIN
        UPDATE Analytics.UiaEntry
		SET [Timestamp] = GETDATE()
		FROM Inserted i
		WHERE Analytics.UiaEntry.Id = i.Id
    END
GO
PRINT N'Creating [iPerform].[Trigger_IdeaToTrade_Timestamp]...';


GO



CREATE TRIGGER [iPerform].[Trigger_IdeaToTrade_Timestamp]
    ON [iPerform].[IdeaToTrade]
    FOR INSERT, UPDATE
    AS
    BEGIN
        UPDATE iPerform.IdeaToTrade
		SET [Timestamp] = GETDATE()
		FROM Inserted i
		WHERE iPerform.IdeaToTrade.IdeaId = i.IdeaId AND iPerform.IdeaToTrade.TradeId = i.TradeId
    END
GO
PRINT N'Creating [TradingData].[IT_FxSerie]...';


GO
CREATE TRIGGER [TradingData].[IT_FxSerie] ON [TradingData].[FxSerie] INSTEAD OF INSERT AS
SET NOCOUNT ON;

UPDATE T
SET 
	T.FxRateInUsd=i.FxRateInUsd	
FROM  [TradingData].[FxSerie] T JOIN inserted i
ON T.CurrencyId=i.CurrencyId AND T.[Date] =i.[Date];

INSERT [TradingData].[FxSerie](CurrencyId,[Date],FxRateInUsd)
SELECT	i.CurrencyId,
		i.Date,
		i.FxRateInUsd
FROM inserted i
WHERE NOT EXISTS(SELECT * FROM  [TradingData].[FxSerie] T WHERE T.CurrencyId=i.CurrencyId AND T.[Date] =i.[Date])
GO
PRINT N'Creating [TradingData].[IT_FundBookEntitySerie]...';


GO
CREATE TRIGGER [TradingData].[IT_FundBookEntitySerie] ON [TradingData].[FundBookEntitySerie] INSTEAD OF INSERT AS
BEGIN
SET NOCOUNT ON;

UPDATE T
SET 
    T.[OpenCapital]  =i.[OpenCapital],               
    T.[CloseCapital]= i.[CloseCapital] 
FROM  [TradingData].[FundBookEntitySerie] T JOIN inserted i
ON T.[FundBookEntityId] = i.[FundBookEntityId] and T.[Date] = i.[Date]



INSERT [TradingData].[FundBookEntitySerie]([FundBookEntityId],[Date],[OpenCapital],[CloseCapital])
SELECT	

	i.[FundBookEntityId],		
	i.[Date],
	i.[OpenCapital],
	i.[CloseCapital]
FROM inserted i
WHERE NOT EXISTS(SELECT * FROM  [TradingData].[FundBookEntitySerie] T WHERE T.FundBookEntityId = i.FundBookEntityId and T.[Date] = i.[Date])
END
GO
PRINT N'Creating [TradingData].[IT_ExcessTimeSerie]...';


GO
CREATE TRIGGER [TradingData].[IT_ExcessTimeSerie] ON [TradingData].[ExcessTimeSerie] INSTEAD OF INSERT AS
BEGIN
SET NOCOUNT ON;

UPDATE T
SET 
    T.[Date]  =i.[Date],               
    T.[BenchmarkId]= i.[BenchmarkId],    
    T.[Sigma] = i.[Sigma],    
    T.[SigmaMove]=i.[SigmaMove],               
    T.[SecurityReturn]=i.[SecurityReturn],    
    T.[BenchmarkReturn]=i.[BenchmarkReturn],
    T.[PreviousUnitPosition]=i.[PreviousUnitPosition],
    T.[SecurityId]= i.[SecurityId]	
FROM  [TradingData].[ExcessTimeSerie] T JOIN inserted i
ON T.SecurityId = i.SecurityId and T.[Date] = i.[Date] and T.[BenchmarkId] = i.[BenchmarkId]



INSERT [TradingData].[ExcessTimeSerie](
    [Date],               
    [BenchmarkId],    
    [Sigma],    
    [SigmaMove],               
    [SecurityReturn],    
    [BenchmarkReturn],
    [PreviousUnitPosition],
    [SecurityId]	)
SELECT	
	i.[Date],               
    i.[BenchmarkId],    
    i.[Sigma],    
    i.[SigmaMove],               
    i.[SecurityReturn],    
    i.[BenchmarkReturn],
    i.[PreviousUnitPosition],
    i.[SecurityId]		
FROM inserted i
WHERE NOT EXISTS(SELECT 1 FROM  [TradingData].[ExcessTimeSerie] T WHERE T.SecurityId = i.SecurityId and T.[Date] = i.[Date]  and T.[BenchmarkId] = i.[BenchmarkId])
END
GO
PRINT N'Creating [TradingData].[IT_TimeSerie]...';


GO
CREATE TRIGGER [TradingData].[IT_TimeSerie] ON [TradingData].[TimeSerie] INSTEAD OF INSERT AS
BEGIN
SET NOCOUNT ON;

UPDATE T
SET 
    T.[Date]  =i.[Date],               
    T.[Open]= i.[Open],    
    T.[AdjustedOpen]=i.[AdjustedOpen],    
    T.[Close]=i.[Close],               
    T.[AdjustedClose]=i.[AdjustedClose],    
    T.[High]=i.[High],
    T.[AdjustedHigh]=i.[AdjustedHigh],
    T.[AdjustedLow]= i.[AdjustedLow],  
    T.[Low]=i.[Low],
    T.[StockReturn]=i.[StockReturn],
    T.[Bbg20DayMedianVolume]=i.[Bbg20DayMedianVolume],
    T.[Bbg90DayVolatility] =i.[Bbg90DayVolatility],
    T.[DayVolume] =i.[DayVolume],
    T.[AdjustedDayVolume]=i.[AdjustedDayVolume],
    T.[PriceToBook]=i.[PriceToBook],
    T.[PriceToCashflow]=i.[PriceToCashflow],
    T.[PriceToEarnings]=i.[PriceToEarnings],
    T.[DividendYield]=i.[DividendYield],
    T.[MarketCap]=i.[MarketCap]
FROM  [TradingData].[TimeSerie] T JOIN inserted i
ON T.SecurityId = i.SecurityId and T.[Date] = i.[Date]



INSERT [TradingData].[TimeSerie](
[SecurityId],
[Date],
[AdjustedOpen],
[AdjustedClose],
[AdjustedHigh],
[AdjustedLow],
[AdjustedDayVolume],
[Open],
[Close],
[High],
[Low],
[DayVolume],
[StockReturn],
[Bbg20DayMedianVolume],
[Bbg90DayVolatility],
[PriceToBook],
[PriceToCashflow],
[PriceToEarnings],
[DividendYield],
[MarketCap])
SELECT	
	i.[SecurityId],
	i.[Date],
	i.[AdjustedOpen],
	i.[AdjustedClose],
	i.[AdjustedHigh],
	i.[AdjustedLow],
	i.[AdjustedDayVolume],
	i.[Open],
	i.[Close],
	i.[High],
	i.[Low],
	i.[DayVolume],
	i.[StockReturn],
	i.[Bbg20DayMedianVolume],
	i.[Bbg90DayVolatility],
	i.[PriceToBook],
	i.[PriceToCashflow],
	i.[PriceToEarnings],
	i.[DividendYield],
	i.[MarketCap]		
FROM inserted i
WHERE NOT EXISTS(SELECT 1 FROM  [TradingData].[TimeSerie] T WHERE T.SecurityId = i.SecurityId and T.[Date] = i.[Date])
END
GO
PRINT N'Creating [iPerform].[AnalysisGroup2UserView]...';


GO
CREATE VIEW [iPerform].[AnalysisGroup2UserView]
AS
SELECT     Membership.[User].LoginName, iPerform.AnalysisGroup.Name
FROM         iPerform.AnalysisGroup2User INNER JOIN
                      iPerform.AnalysisGroup ON iPerform.AnalysisGroup2User.AnalysisGroupId = iPerform.AnalysisGroup.Id INNER JOIN
                      Membership.[User] ON iPerform.AnalysisGroup2User.UserId = Membership.[User].Id;
GO
PRINT N'Creating [iPerform].[JoinedIdea]...';


GO
CREATE VIEW [iPerform].[JoinedIdea]
	AS  SELECT        Analytics.Idea.Id, Analytics.Idea.FundBookEntityId, Analytics.Idea.Type, Idea_1.OwnerUserId, Idea_1.StrategyInitTemplateId, Idea_1.Private, Idea_1.Name, 
								 CASE WHEN Closed IS NOT NULL THEN Closed 
								 ELSE ~HasOpenPosition  END 
								 AS Closed
		FROM            Analytics.Idea 
		LEFT JOIN
								 iPerform.Idea AS Idea_1 ON Analytics.Idea.Id = Idea_1.Id
GO
PRINT N'Creating [TradingData].[JoinedTrade]...';


GO
CREATE VIEW [TradingData].[JoinedTrade]
WITH SCHEMABINDING
AS
SELECT     t.FundBookEntityId, t.SecurityId, t.Id, t.Quantity, t.Date, t.CurrencyId, t.Price, t.FxRate, bs2b.BenchmarkId
FROM         TradingData.Trade AS t INNER JOIN
                      TradingData.BookSecurityToBenchmark AS bs2b ON t.FundBookEntityId = bs2b.FundBookEntityId AND t.SecurityId = bs2b.SecurityId INNER JOIN
                         TradingData.Security AS s ON t.SecurityId = s.Id INNER JOIN
                         TradingData.SecurityClass AS sc ON s.ClassId = sc.Id
WHERE        (sc.Include = 1)
GO
PRINT N'Creating [TradingData].[JoinedTrade].[JoinedTradeIndex]...';


GO
CREATE UNIQUE CLUSTERED INDEX [JoinedTradeIndex]
    ON [TradingData].[JoinedTrade]([Id] ASC);


GO
PRINT N'Creating [TradingData].[UsedSecurities]...';


GO
CREATE VIEW [TradingData].[UsedSecurities]
WITH SCHEMABINDING
AS
SELECT     s.Id, s.Isin, s.Ric, s.Mic, s.Sedol, s.CustomIdentificator, s.Name, s.LongName, s.ClassId, s.CurrencyId, s.EconomicSectorId, s.CountryId, s.ParentId, s.ParentIdentify, s.ParentIdentifyType, s.Type, s.Ticker
FROM       TradingData.Security AS s  INNER JOIN
           TradingData.SecurityClass AS sc ON s.ClassId = sc.Id
WHERE      sc.Include = 1
GO
PRINT N'Creating [TradingData].[UsedSecurities].[UsedSecuritiesIndex]...';


GO
CREATE UNIQUE CLUSTERED INDEX [UsedSecuritiesIndex]
    ON [TradingData].[UsedSecurities]([Id] ASC);


GO
PRINT N'Creating [TradingData].[SecurityView]...';


GO
CREATE VIEW TradingData.SecurityView
WITH SCHEMABINDING 
AS
SELECT        Extent1.Id, Extent1.Name, Extent1.Ric, Extent1.LongName, Extent1.Isin, Extent1.Ticker, Extent2.Name AS Class, Extent3.EconomicSectorDescription AS Sector, 
                         Extent4.CountryDescription AS Country, Extent5.Name AS Currency
FROM            TradingData.Security AS Extent1 INNER JOIN
                         TradingData.SecurityClass AS Extent2 ON Extent1.ClassId = Extent2.Id INNER JOIN
                         TradingData.EconomicSector AS Extent3 ON Extent1.EconomicSectorId = Extent3.Id INNER JOIN
                         TradingData.Country AS Extent4 ON Extent1.CountryId = Extent4.Id INNER JOIN
                         TradingData.Currency AS Extent5 ON Extent1.CurrencyId = Extent5.Id
WHERE        (Extent2.Include = 1)
GO
PRINT N'Creating [TradingData].[SecurityView].[SecurityViewIndex]...';


GO
CREATE UNIQUE CLUSTERED INDEX [SecurityViewIndex]
    ON [TradingData].[SecurityView]([Id] ASC);


GO
PRINT N'Creating Full-text Index on [TradingData].[SecurityView]...';


GO
CREATE FULLTEXT INDEX ON [TradingData].[SecurityView]
    ([Name] LANGUAGE 1033, [Ric] LANGUAGE 1033, [LongName] LANGUAGE 1033, [Isin] LANGUAGE 1033, [Class] LANGUAGE 1033, [Sector] LANGUAGE 1033, [Country] LANGUAGE 1033, [Currency] LANGUAGE 1033, [Ticker] LANGUAGE 1033)
    KEY INDEX [SecurityViewIndex]
    ON [IIP];


GO
PRINT N'Creating [DataConsistency].[DetectInvalidIdeaData]...';


GO
CREATE PROCEDURE [DataConsistency].[DetectInvalidIdeaData] @FundBookEntityId int = NULL, @IdeaName nvarchar(150) = NULL, @IdeaId uniqueidentifier = NULL
AS

WITH minTradeDates AS
(
	SELECT MIN(t.[Date]) AS Date, InvestmentId, t.SecurityId 
	FROM Analytics.TradedQuantity tq
	INNER JOIN TradingData.Trade t ON tq.TradeId = t.Id	
	GROUP BY InvestmentId, t.SecurityId
),
maxTradeDates AS
(
	SELECT MAX(t.[Date]) AS Date, InvestmentId, t.SecurityId 
	FROM Analytics.TradedQuantity tq
	INNER JOIN TradingData.Trade t ON tq.TradeId = t.Id	
	GROUP BY InvestmentId, t.SecurityId
)

SELECT * FROM
(
	SELECT idea.FundBookEntityId, idea.Id AS IdeaId, idea.Name AS IdeaName, inv.Id AS InvestmentId, t.SecurityId, 
		CONVERT(varchar(30),minTD.[Date],126) AS FirstTradeDate, CONVERT(varchar(30),maxTD.[Date],126) AS LastTradeDate,
		NoTimeSeries = CASE WHEN
							(								
								SELECT COUNT(*)
								FROM
								(
									SELECT 
										[Date], [Close], [MarketCap], [StockReturn]
									FROM TradingData.TimeSerie ts
									WHERE SecurityId = t.SecurityId AND [Date] <= t.[Date] 
										AND [Close] IS NOT NULL
									UNION
									SELECT TOP 1
										[Date], [Close], [MarketCap], [StockReturn]
									FROM TradingData.TimeSerie
									WHERE SecurityId = t.SecurityId AND [Date] > t.[Date]
										AND [Date] <= (SELECT MAX([Date])
														FROM
														(
															SELECT TOP 4 [Date] -- using 4 days window
															FROM TradingData.TimeSerie
															WHERE SecurityId = t.SecurityId AND [Date] > t.[Date]
																AND [Close] IS NOT NULL
															ORDER BY [Date]
														)s)
										AND [Close] IS NOT NULL                                
									ORDER BY [Date]
								) s
							) > 0 THEN 'false' ELSE 'true' END,
		NoFundBookSeries = CASE WHEN 
							(
								SELECT COUNT(*) 
								FROM TradingData.FundBookEntitySerie
								WHERE FundBookEntityId = idea.FundBookEntityId AND [Date] <= t.[Date]
									AND OpenCapital IS NOT NULL AND OpenCapital > 0
							) > 0 THEN 'false' ELSE 'true' END,
		NoBenchmarkSeries = CASE WHEN 
							(
								SELECT COUNT(*)
								FROM TradingData.TimeSerie ts
								WHERE SecurityId = bsb.BenchmarkId AND [Date] <= t.[Date] 
									AND [Close] IS NOT NULL		
							) > 0 THEN 'false' ELSE 'true' END,
		NoFxRates = CASE WHEN
							(
								SELECT COUNT(*)
								FROM TradingData.FxSerie fxFund, 
									 TradingData.FxSerie fxSecurity 
								WHERE fxSecurity.CurrencyId = s.CurrencyId AND fxFund.CurrencyId = fe.CurrencyId 
									 AND fxFund.[Date] = fxSecurity.[Date] AND fxFund.[Date] <= t.[Date]
							) > 0 THEN 'false' ELSE 'true' END,
		NoExcessTimeSeries = CASE WHEN
							(
								SELECT COUNT(*)
								FROM TradingData.ExcessTimeSerie ets
								WHERE SecurityId = bsb.SecurityId AND BenchmarkId = bsb.BenchmarkId AND fe.Id = bsb.FundBookEntityId AND [Date] <= t.[Date] 
							) > 0 THEN 'false' ELSE 'true' END
	FROM Analytics.Idea idea
	inner join Analytics.Investment inv ON idea.Id = inv.IdeaId
	inner join Analytics.TradedQuantity tq ON inv.Id = tq.InvestmentId
	inner join TradingData.Trade t ON tq.TradeId = t.Id
	inner join minTradeDates minTD ON minTD.InvestmentId = inv.Id AND minTD.SecurityId = t.SecurityId AND minTD.[Date] = t.[Date]
	inner join maxTradeDates maxTD ON maxTD.InvestmentId = inv.Id AND maxTD.SecurityId = t.SecurityId
	inner join TradingData.BookSecurityToBenchmark bsb ON bsb.FundBookEntityId = idea.FundBookEntityId AND bsb.SecurityId = t.SecurityId
	inner join TradingData.Security s ON t.SecurityId = s.Id	
	inner join TradingData.FundBookEntity fe ON idea.FundBookEntityId = fe.Id
	where idea.FundBookEntityId IN
			(
				SELECT DISTINCT FundBookEntityId FROM iPerform.AnalysisGroup2FundBookEntity
			)
			AND idea.FundBookEntityId = ISNULL(@FundBookEntityId, idea.FundBookEntityId)
			AND idea.Name LIKE '%' + ISNULL(@IdeaName, idea.Name) + '%' 
			AND idea.Id = ISNULL(@IdeaId, idea.Id)
) s
WHERE s.NoTimeSeries = 'true' OR s.NoFundBookSeries = 'true' OR s.NoBenchmarkSeries = 'true' OR s.NoFxRates = 'true' OR s.NoExcessTimeSeries = 'true'
ORDER BY s.FirstTradeDate
GO
PRINT N'Creating [iPerform].[GetReCreateForeignKeyCommands]...';


GO
CREATE PROCEDURE [iPerform].[GetReCreateForeignKeyCommands]
@schema [sysname]
AS
SELECT fk.delete_referential_action,
	fk.delete_referential_action_desc,
	fk.update_referential_action,
	fk.update_referential_action_desc,
	'ALTER TABLE ['+SCHEMA_NAME(o.schema_id)+'].['+o.name+'] ADD CONSTRAINT ['+fk.name+'] FOREIGN KEY (['+c.name+']) 
	REFERENCES ['+SCHEMA_NAME(refob.schema_id)+'].['+refob.name+'](['+refcol.name+'])' As ReCreateCommand
	FROM sys.foreign_key_columns fkc
	inner join sys.foreign_keys fk
			  ON fkc.constraint_object_id = fk.object_id
	inner join sys.objects o
			  ON fk.parent_object_id = o.object_id
	inner join sys.columns c
			  ON      fkc.parent_column_id = c.column_id and
					   o.object_id = c.object_id
	inner join sys.objects refob
			  ON fkc.referenced_object_id = refob.object_id
	inner join sys.columns refcol
			  ON fkc.referenced_column_id = refcol.column_id and
					   fkc.referenced_object_id = refcol.object_id
	WHERE 			  
			  SCHEMA_NAME(o.schema_id)  = @schema
GO
PRINT N'Creating [iPerform].[GetLastRevisionTemplateQuestionIds]...';


GO
CREATE PROCEDURE [iPerform].[GetLastRevisionTemplateQuestionIds]
@InitTemplateId INT=-1, @IdeaId uniqueidentifier=null, @UserId INT=null
AS
DECLARE @IInitTemplateId INT
DECLARE @LastTemplateId INT
DECLARE @tmptable table(QuestionGroupId INT)

IF @InitTemplateId > 0
BEGIN	
	SET @IInitTemplateId = @InitTemplateId
END

IF @IdeaId is not null
BEGIN	
	SELECT @IInitTemplateId = StrategyInitTemplateId FROM iPerform.Idea
	WHERE Id = @IdeaId		
END

--SELECT TOP 1 @LastTemplate = Id FROM iPerform.Template
--WHERE ParentID = @IInitTemplateId OR ID = @IInitTemplateId
--ORDER BY ID DESC

SELECT DISTINCT TOP 1 @LastTemplateId = t.Id FROM
	iPerform.Template t
INNER JOIN
	iPerform.TemplateAnswer ta
ON
	t.Id = ta.TemplateId
WHERE
	t.ParentID = @IInitTemplateId OR t.ID = @IInitTemplateId
ORDER BY t.Id DESC

--Select only Active Groups from Last Current Template
INSERT INTO @tmptable(QuestionGroupId)
SELECT QuestionGroupId FROM iPerform.Template2QuestionGroup
WHERE TemplateId = @LastTemplateId AND Active = 1

SELECT q.Id
FROM
    iPerform.Question q
--INNER JOIN
--    iPerform.QuestionGroup qg
--ON
--    q.QuestionGroupId = qg.Id
INNER JOIN
	iPerform.Template2Question t2q
ON
	t2q.QuestionId = q.Id					                        
WHERE q.QuestionGroupId IN (SELECT QuestionGroupId FROM @tmptable)
AND (q.UserId IS NULL OR q.UserId = @UserId)
AND t2q.TemplateId = @LastTemplateId
GO
PRINT N'Creating [iPerform].[GetInitTemplates]...';


GO
CREATE PROCEDURE [iPerform].[GetInitTemplates]
@DefaultTemplateID INT, @UserID INT=NULL
AS
SELECT * FROM 
		iPerform.Template 
	WHERE 
		ParentID = @DefaultTemplateID
	AND
		(@UserID IS NULL OR UserID = @UserID)

--@QuestionGroupID int,
--@UserID int = NULL
--AS
--    SELECT * FROM 
--		iPerform.Template 
--	WHERE 
--		ParentID = (SELECT ID FROM iPerform.Template WHERE QuestionGroupID = @QuestionGroupID AND UserID IS NULL)
--	AND
--		(@UserID IS NULL OR UserID = @UserID)
GO
PRINT N'Creating [iPerform].[GetDropForeignKeyCommands]...';


GO
CREATE PROCEDURE [iPerform].[GetDropForeignKeyCommands]
@schema [sysname]
AS
SELECT 
			  'ALTER TABLE ['+SCHEMA_NAME(o.schema_id)+'].['+ o.name+'] DROP CONSTRAINT ['+fk.name+']' As DropCommands
	FROM sys.foreign_keys fk
	inner join sys.objects o
			  ON fk.parent_object_id = o.object_id
	WHERE 			  
			  SCHEMA_NAME(o.schema_id) = @schema
GO
PRINT N'Creating [iPerform].[GetAnswerGridQuestionGroupIds]...';


GO
CREATE PROCEDURE [iPerform].[GetAnswerGridQuestionGroupIds]
@InitTemplateId INT=-1, @IdeaId uniqueidentifier=null
AS
DECLARE @tablevar table(QuestionGroupId INT, RowNumber INT)
DECLARE @tableTemplateIDs table(TemplateId INT)

IF @IdeaId is not null
BEGIN
	INSERT INTO @tableTemplateIDs(TemplateId)
		SELECT DISTINCT			
			t.TemplateId			
		FROM
			iPerform.IdeaToTemplateAnswer sg2ta
		INNER JOIN
			iPerform.TemplateAnswer t
		ON 
			sg2ta.TemplateAnswerId = t.Id
		WHERE
			IdeaId = @IdeaId
END

IF @InitTemplateId > 0
BEGIN
	INSERT INTO @tableTemplateIDs(TemplateId)
		SELECT t.ID FROM 
			iPerform.Template t
		WHERE
			t.ParentId = @InitTemplateId
		OR
			t.Id = @InitTemplateId
END 

INSERT INTO @tablevar(QuestionGroupId, RowNumber)
SELECT QuestionGroupId, ROW_NUMBER() OVER (ORDER BY TemplateId DESC) AS RowNumber
FROM
	iPerform.Template2QuestionGroup
WHERE 
	TemplateId IN ( SELECT * FROM @tableTemplateIDs )
--AND Active = 1
--ORDER BY TemplateId DESC

-- the code below for preserving order
DECLARE @tabledistinct table(QuestionGroupId INT, RowNumber INT)

DECLARE @var INT
DECLARE curs CURSOR FOR SELECT DISTINCT QuestionGroupId FROM @tablevar
OPEN curs
FETCH NEXT FROM curs INTO @var
WHILE @@FETCH_STATUS = 0
BEGIN
	INSERT INTO @tabledistinct(QuestionGroupId, RowNumber) SELECT TOP 1 QuestionGroupId, RowNumber
	FROM @tablevar
	WHERE QuestionGroupId = @var
	
	FETCH NEXT FROM curs INTO @var
END

CLOSE curs
DEALLOCATE curs

SELECT QuestionGroupId FROM @tabledistinct
ORDER BY RowNumber
GO
PRINT N'Creating [iPerform].[InitTradesToTemplateAnswerStartDates]...';


GO
--CREATE PROCEDURE [iPerform].[InitTradesToTemplateAnswerStartDates]
--@InitDate DATETIME
--AS
--TRUNCATE TABLE iPerform.TradesToTemplateAnswerStartDate;

--INSERT INTO iPerform.TradesToTemplateAnswerStartDate
--(FundBookEntityId, SecurityId, [Date])
--SELECT
--	FundBookEntityId,
--	SecurityId,
--	DATEADD(d, 1, Max([Date])) AS [Date]
--FROM
--	TradingData.OriginalPosition
--WHERE
--	[Date] <= @InitDate
--	AND
--	CloseQuantity = 0
--GROUP BY
--	SecurityId,
--	FundBookEntityId

CREATE PROCEDURE [iPerform].[InitTradesToTemplateAnswerStartDates]
@InitDate datetime
AS
TRUNCATE TABLE iPerform.TradesToTemplateAnswerStartDate;

INSERT INTO iPerform.TradesToTemplateAnswerStartDate
(FundBookEntityId, SecurityId, LastTradeDate, [Date])
SELECT findtrades.*, findpositions.[Date] FROM
(
	(
		SELECT
			FundBookEntityId,
			SecurityId,
			DATEADD(d, 1, Max([Date])) AS [Date]
		FROM
			TradingData.Position
		WHERE
			[Date] <= @InitDate AND CloseQuantity = 0
		GROUP BY
			SecurityId,
			FundBookEntityId
	) findpositions
	RIGHT JOIN
	(
		SELECT FundBookEntityId, SecurityId, MAX([Date]) As LastTradeDate
		FROM TradingData.Trade
		WHERE Quantity != 0		--gets last date of a trade with quantity != 0
		GROUP BY
			SecurityId,
			FundBookEntityId
	 ) findtrades
	ON 
		findpositions.FundBookEntityId = findtrades.FundBookEntityId
	AND
		findpositions.SecurityId = findtrades.SecurityId
)
GO
PRINT N'Creating [iPerform].[GetUserFundBookEntities]...';


GO
CREATE PROCEDURE [iPerform].[GetUserFundBookEntities]
@UserId INT
AS
-- Select all books attached to user
-- We don't need additionaly return books in matched funds, analysis group contains funds only if they don't have books

SELECT fbe.* FROM 
	TradingData.FundBookEntity fbe
INNER JOIN
	iPerform.AnalysisGroup2FundBookEntity ag2fbe
ON
	ag2fbe.FundBookEntityId = fbe.Id
INNER JOIN
	iPerform.AnalysisGroup2User ag2u
ON
	ag2u.AnalysisGroupId = ag2fbe.AnalysisGroupId
INNER JOIN
	Membership.[User] u
ON
	ag2u.UserId = u.ID
WHERE
	u.ID = @UserId
GO
PRINT N'Creating [iPerform].[GetTemplatesForQuestionGroup]...';


GO
CREATE PROCEDURE [iPerform].[GetTemplatesForQuestionGroup]
@UserID INT=null, @QuestionGroupID INT
AS
SELECT 
	* 
FROM
	iPerform.Template
WHERE
	QuestionGroupID = @QuestionGroupID
AND
	(@UserID IS NULL OR UserID = @UserID)
GO
PRINT N'Creating [iPerform].[GetTemplateHistory]...';


GO
CREATE PROCEDURE [iPerform].[GetTemplateHistory]
@TemplateId INT
AS
SELECT
               ID,
               AddedDT
       FROM
               iPerform.Template t
       WHERE
       (
               --parentID equal parentID of current template
               (t.ParentID = (SELECT ParentId FROM iPerform.Template WHERE Id = @TemplateId)
               AND
               (t.TypeID = 1
                       OR
                       (t.TypeID = 2
                               AND
                               (SELECT UserID FROM iPerform.Template WHERE ID = t.ParentID) IS NOT NULL
                       )


               )
               )
       OR
               t.ID = (SELECT ParentId FROM iPerform.Template WHERE Id= @TemplateId)
       OR
               t.ParentID = @TemplateId
       OR
               --select root template
               t.ID = (SELECT ParentId FROM iPerform.Template WHERE Id = (SELECT
ParentId FROM iPerform.Template WHERE Id = @TemplateId))
       OR
               --select current template into history
               t.ID = @TemplateId
       )
       AND
       (
               t.UserId = (SELECT UserId FROM iPerform.Template WHERE Id = @TemplateId)

               --no need to load default template, start from init
               --OR
               --t.UserId IS NULL
       )
GO
PRINT N'Creating [Membership].[GetUsers]...';


GO
CREATE PROCEDURE [Membership].[GetUsers]
AS
BEGIN
	SELECT * FROM Membership.[User]
END
GO
PRINT N'Creating [Membership].[GetRolesForUser]...';


GO
CREATE PROCEDURE [Membership].[GetRolesForUser]
@UserId INT, @EntityId INT=null
AS
BEGIN

	SET NOCOUNT ON;
	SELECT r.* FROM 
		Membership.[Role] r
	INNER JOIN
		Membership.User2Role u2r
	ON
		r.Id = u2r.RoleId
	WHERE
		u2r.UserId = @UserId
	AND
		(u2r.StartDT IS NULL OR DateAdd(day, datediff(day,0, u2r.StartDT), 0) <= DateAdd(day, datediff(day,0, getdate()), 0))
	AND
		(u2r.EndDT IS NULL OR DateAdd(day, datediff(day,0, u2r.EndDT), 0) >= DateAdd(day, datediff(day,0, getdate()), 0))
	AND
		(@EntityId IS NULL OR r.EntityId = @EntityId OR r.EntityId is null)
END
GO
PRINT N'Creating [Membership].[GetContactForUser]...';


GO
CREATE PROCEDURE [Membership].[GetContactForUser]
@UserId INT
AS
BEGIN

	SET NOCOUNT ON;
	SELECT * FROM 
		Membership.Contact c
	INNER JOIN
		Membership.[User] u
	ON
		u.ContactId = c.Id
	WHERE
		u.Id = @UserId
END
GO
PRINT N'Creating [Membership].[Authenticate]...';


GO
CREATE PROCEDURE [Membership].[Authenticate]
@LoginName NVARCHAR (150), @Password NVARCHAR (150), @EntityId INT=null
AS
BEGIN
	SET NOCOUNT ON;
	SELECT * FROM Membership.[User] u
	inner join Membership.User2Role u2r on u.Id = u2r.UserId
	inner join Membership.[Role] r on u2r.RoleId = r.Id
	WHERE
		u.LoginName = @LoginName
		AND
		u.[Password] = @Password		
		AND 
		(@EntityId is null OR r.EntityId is null OR r.EntityId = @EntityId)
END
GO
PRINT N'Creating [Membership].[AddUser]...';


GO
CREATE PROCEDURE [Membership].[AddUser]
@LoginName NVARCHAR (1), @Password NVARCHAR (1), @Theme NVARCHAR (1), @ContactId INT
AS
BEGIN
	INSERT INTO [Membership].[User] 
		(LoginName, [Password], Theme, ContactId)
	VALUES
		(@LoginName, @Password, @Theme, @ContactId)
END
GO
PRINT N'Creating [TradingData].[UpdateFxRate]...';


GO
CREATE PROCEDURE [TradingData].[UpdateFxRate]
AS

DECLARE @TradeCount int;
Declare @Id int;
Declare @SecurityId int;
Declare @fxRate decimal(18,10);
Declare @CurrencyId int;
Declare @Date smalldatetime;
Declare @MinCount Int = 1;

select @TradeCount =  count(id) from [TradingData].[Trade] where FxRate is null;

Declare @TempEmpTbl Table (RowId int identity, id int, SecurityId int,CurrencyId int,[Date] smalldatetime);
Insert into @TempEmpTbl Select id,SecurityId,CurrencyId,[Date] from [TradingData].[Trade] where FxRate is null;

Declare @TempEmpCount Int = (Select Count(RowId) From @TempEmpTbl);


While(@TempEmpCount >= @MinCount)
begin
 select @Id =id, @SecurityId=SecurityId,@CurrencyId=CurrencyId,@Date=[Date] From @TempEmpTbl Where RowId = @MinCount
 select @fxRate = FxRateInUsd from [TradingData].[FxSerie] where [Date]=@Date and CurrencyId=@CurrencyId
 if(@fxRate is not null)
 begin
	update [TradingData].[Trade] set FxRate = @fxRate where id=@id;
 end
 select @Id =0, @SecurityId=0, @CurrencyId=0, @Date=NULL;

 Set @MinCount = @MinCount + 1;
end
GO
PRINT N'Creating [TradingData].[UpdateParentId]...';


GO
CREATE PROCEDURE [TradingData].[UpdateParentId]
AS
SET NOCOUNT ON;
DECLARE @SecurityCount int;
select @SecurityCount =  count(id) from [TradingData].[Security] where ParentId = 0 or ParentId is NULL;
PRINT @SecurityCount

Declare @TempEmpTbl Table (RowId int identity, id int, Isin varchar(20),Ric varchar(20)
							,Mic varchar(20),Sedol varchar(20),CustomIdentificator varchar(20), ParentIdentify varchar(20),ParentIdentifyType varchar(20));
Declare @TempEmpTblId Table (Id int );

Insert into @TempEmpTbl Select id,Isin,Ric,Mic,Sedol,CustomIdentificator,ParentIdentify, ParentIdentifyType from [TradingData].[Security] where (ParentId = 0) and ParentIdentify<>'';


Declare @TempEmpCount Int = (Select Count(RowId) From @TempEmpTbl);
Print @TempEmpCount
Declare @MinCount Int = 1;
Declare @Id int;
Declare @ParentId int;
Declare @ParentIdentifyType varchar(20);
Declare @ParentIdentify varchar(20);
Declare @sql nvarchar(1000);
DECLARE @ParmDefinition nvarchar(500);

While(@TempEmpCount >= @MinCount)
Begin
	
	select @Id=id,@ParentIdentify=ParentIdentify,@ParentIdentifyType=ParentIdentifyType From @TempEmpTbl Where RowId = @MinCount

	Set @sql = N'select  @retvalOUT =  id from  [TradingData].[Security] where '+@ParentIdentifyType+N' = '''+@ParentIdentify+N''''
	SET @ParmDefinition = N'@retvalOUT int OUTPUT';
	EXEC sp_executesql @sql, @ParmDefinition, @retvalOUT=@ParentId OUTPUT;
	
	update [TradingData].[Security] set ParentId =@ParentId where id = @Id and @ParentId!= @Id
	update [TradingData].[Security] set ParentId =NULL where id = @Id and @ParentId= @Id
	select @ParentId =0
    Set @MinCount = @MinCount + 1;
End
GO
PRINT N'Creating [dbo].[DropPrimaryKey]...';


GO
CREATE PROCEDURE [dbo].[DropPrimaryKey]
@tableName VARCHAR (255)
AS
/*
       Drop the primary key on @TableName

http://gen5.info/q/

       Version 1.1
       June 9, 2008
    */

    DECLARE @pkName Varchar(255)

    SET @pkName= (
        SELECT [name] FROM sysobjects
            WHERE [xtype] = 'PK'
            AND [parent_obj] = OBJECT_ID(@tableName)
    )
    DECLARE @dropSql varchar(4000)

    SET @dropSql=
        'ALTER TABLE '+@tableName+'
            DROP CONSTRAINT ['+@PkName+']'
    EXEC(@dropSql)
GO
-- Refactoring step to update target server with deployed transaction logs


GO
-- Schema scripts

-- Data scripts
IF NOT EXISTS (SELECT * FROM [Membership].[User] ) 

BEGIN
SET IDENTITY_INSERT [Membership].Entity ON
insert into [Membership].Entity (id,Name) values(0,'Management Console')
insert into [Membership].Entity (id,Name) values(1,'Edge')
insert into [Membership].Entity (id,Name) values(2,'Compass')
SET IDENTITY_INSERT [Membership].Entity OFF

insert into [Membership].Role (Title,EntityId,Description)	values('Admin',0,'Admin')
insert into [Membership].Role (Title,EntityId,Description)	values('Admin',1,'Admin')
insert into [Membership].Role (Title,EntityId,Description)	values('Admin',2,'Admin')
insert into [Membership].Role (Title,EntityId,Description)	values('User',1,'User')
insert into [Membership].Role (Title,EntityId,Description)	values('User',2,'User')
insert into [Membership].Role (Title,EntityId,Description)	values('Supervisor',1,'Supervisor')

insert into [Membership].Contact (Title,FirstName,LastName,Email,Tel,Mobile)	values ('Mr','Admin',' ','admin@invest-intelligence.com',' ',' ')
insert into [Membership].[User] (LoginName,Password,Theme,ContactId)	values('Admin','1nve5t###','cupertino',1)

insert into [Membership].[User2Role] (UserId,RoleId)	values(1,1)
insert into [Membership].[User2Role] (UserId,RoleId)	values(1,2)
insert into [Membership].[User2Role] (UserId,RoleId)	values(1,3)


END

--Set Unsigned Value for SecurityClass
IF NOT EXISTS (SELECT * FROM [TradingData].[SecurityClass] WHERE id = 0) 
	BEGIN
		SET IDENTITY_INSERT [TradingData].[SecurityClass] ON
	
			insert into	[TradingData].[SecurityClass]
			(id,[Name],[EnumName],[Include],[IncludeInCalc])
			values(0,'Unclassified','Unclassified','false','false')
		SET IDENTITY_INSERT [TradingData].[SecurityClass] OFF
	END


-- Set Unsigned value for Manager
IF NOT EXISTS (SELECT * FROM [TradingData].[Manager] WHERE id = 0) 
	BEGIN
		SET IDENTITY_INSERT [TradingData].[Manager] ON
	
			insert into	[TradingData].[Manager]
			(id,[Name])
			values(0,'Unassigned')

		SET IDENTITY_INSERT [TradingData].[Manager] OFF
	END

--Set Unsigned Value for EconomicSector
IF NOT EXISTS (SELECT * FROM [TradingData].[EconomicSector] WHERE id = 0) 
	BEGIN
		SET IDENTITY_INSERT [TradingData].[EconomicSector] ON
	
			insert into	[TradingData].[EconomicSector]
			(Id,[EconomicSectorId],[EconomicSectorDescription],[BusinesSectorId],
			[BusinesSectorDescription],[IndustryGroupId],[IndustryGroupDescription])
			values(0,0,'Unclassified',0,'Unclassified',0,'Unclassified')

		SET IDENTITY_INSERT [TradingData].[EconomicSector] OFF
	END
declare @countrycount int;
select @countrycount =  COUNT(*) FROM [TradingData].[Country]
IF (@countrycount =0 )
	BEGIN
		SET IDENTITY_INSERT [TradingData].[Country] ON

		INSERT [TradingData].[Country] ([Id], [CountryCode], [CountryDescription], [Region]) VALUES (0, N'', N'Unknown Country', N'Unknown Region')

		INSERT [TradingData].[Country] ([Id], [CountryCode], [CountryDescription], [Region]) VALUES (1, N'AR', N'Argentina', N'Latin America')
		
		INSERT [TradingData].[Country] ([Id], [CountryCode], [CountryDescription], [Region]) VALUES (2, N'AU', N'Australia', N'Australasia')
		
		INSERT [TradingData].[Country] ([Id], [CountryCode], [CountryDescription], [Region]) VALUES (3, N'AT', N'Austria', N'Europe Excl UK')
		
		INSERT [TradingData].[Country] ([Id], [CountryCode], [CountryDescription], [Region]) VALUES (4, N'BE', N'Belgium', N'Europe Excl UK')
		
		INSERT [TradingData].[Country] ([Id], [CountryCode], [CountryDescription], [Region]) VALUES (5, N'BM', N'Bermuda', N'North America')
		
		INSERT [TradingData].[Country] ([Id], [CountryCode], [CountryDescription], [Region]) VALUES (6, N'BR', N'Brazil', N'Latin America')
		
		INSERT [TradingData].[Country] ([Id], [CountryCode], [CountryDescription], [Region]) VALUES (7, N'CA', N'Canada', N'North America')
		
		INSERT [TradingData].[Country] ([Id], [CountryCode], [CountryDescription], [Region]) VALUES (8, N'CN', N'China', N'Asia Excl Japan')
		
		INSERT [TradingData].[Country] ([Id], [CountryCode], [CountryDescription], [Region]) VALUES (9, N'CZ', N'Czech Republic', N'Europe Excl UK')
		
		INSERT [TradingData].[Country] ([Id], [CountryCode], [CountryDescription], [Region]) VALUES (10, N'DK', N'Denmark', N'Europe Excl UK')
		
		INSERT [TradingData].[Country] ([Id], [CountryCode], [CountryDescription], [Region]) VALUES (11, N'EU', N'Euro countries', N'Europe Excl UK')
		
		INSERT [TradingData].[Country] ([Id], [CountryCode], [CountryDescription], [Region]) VALUES (12, N'FI', N'Finland', N'Europe Excl UK')
		
		INSERT [TradingData].[Country] ([Id], [CountryCode], [CountryDescription], [Region]) VALUES (13, N'FR', N'France', N'Europe Excl UK')
		
		INSERT [TradingData].[Country] ([Id], [CountryCode], [CountryDescription], [Region]) VALUES (14, N'GE', N'Germany', N'Europe Excl UK')
		
		INSERT [TradingData].[Country] ([Id], [CountryCode], [CountryDescription], [Region]) VALUES (15, N'GR', N'Greece', N'Europe Excl UK')
		
		INSERT [TradingData].[Country] ([Id], [CountryCode], [CountryDescription], [Region]) VALUES (16, N'GU', N'Guernsey', N'United Kingdom')
		
		INSERT [TradingData].[Country] ([Id], [CountryCode], [CountryDescription], [Region]) VALUES (17, N'HK', N'Hong Kong', N'Asia Excl Japan')
		
		INSERT [TradingData].[Country] ([Id], [CountryCode], [CountryDescription], [Region]) VALUES (18, N'HU', N'Hungary', N'Europe Excl UK')
		
		INSERT [TradingData].[Country] ([Id], [CountryCode], [CountryDescription], [Region]) VALUES (19, N'IN', N'India', N'Asia Excl Japan')
		
		INSERT [TradingData].[Country] ([Id], [CountryCode], [CountryDescription], [Region]) VALUES (20, N'IR', N'Ireland', N'Europe Excl UK')

		INSERT [TradingData].[Country] ([Id], [CountryCode], [CountryDescription], [Region]) VALUES (21, N'IL', N'Israel', N'Middle East')
		
		INSERT [TradingData].[Country] ([Id], [CountryCode], [CountryDescription], [Region]) VALUES (22, N'IT', N'Italy', N'Europe Excl UK')
		
		INSERT [TradingData].[Country] ([Id], [CountryCode], [CountryDescription], [Region]) VALUES (23, N'JP', N'Japan', N'Japan')

		INSERT [TradingData].[Country] ([Id], [CountryCode], [CountryDescription], [Region]) VALUES (25, N'LU', N'Luxembourg', N'Europe Excl UK')
		
		INSERT [TradingData].[Country] ([Id], [CountryCode], [CountryDescription], [Region]) VALUES (26, N'MA', N'Macau', N'Asia Excl Japan')
		
		INSERT [TradingData].[Country] ([Id], [CountryCode], [CountryDescription], [Region]) VALUES (27, N'ME', N'Mexico', N'Latin America')
		
		INSERT [TradingData].[Country] ([Id], [CountryCode], [CountryDescription], [Region]) VALUES (28, N'NL', N'Netherlands', N'Europe Excl UK')
		
		INSERT [TradingData].[Country] ([Id], [CountryCode], [CountryDescription], [Region]) VALUES (29, N'NO', N'Norway', N'Europe Excl UK')
		
		INSERT [TradingData].[Country] ([Id], [CountryCode], [CountryDescription], [Region]) VALUES (30, N'PE', N'Peru', N'Latin America')
		
		INSERT [TradingData].[Country] ([Id], [CountryCode], [CountryDescription], [Region]) VALUES (31, N'PH', N'Philippines', N'Asia Excl Japan')
		
		INSERT [TradingData].[Country] ([Id], [CountryCode], [CountryDescription], [Region]) VALUES (32, N'PL', N'Poland', N'Europe Excl UK')
		
		INSERT [TradingData].[Country] ([Id], [CountryCode], [CountryDescription], [Region]) VALUES (33, N'PT', N'Portugal', N'Europe Excl UK')
		
		INSERT [TradingData].[Country] ([Id], [CountryCode], [CountryDescription], [Region]) VALUES (34, N'PU', N'Puerto Rico (US)', N'Latin America')
		
		INSERT [TradingData].[Country] ([Id], [CountryCode], [CountryDescription], [Region]) VALUES (35, N'RU', N'Russian Federation', N'Europe Excl UK')
		
		
		INSERT [TradingData].[Country] ([Id], [CountryCode], [CountryDescription], [Region]) VALUES (37, N'SO', N'South Africa', N'Africa')
		
		INSERT [TradingData].[Country] ([Id], [CountryCode], [CountryDescription], [Region]) VALUES (38, N'SP', N'Spain', N'Europe Excl UK')
		
		INSERT [TradingData].[Country] ([Id], [CountryCode], [CountryDescription], [Region]) VALUES (39, N'SW', N'Sweden', N'Europe Excl UK')
		
		INSERT [TradingData].[Country] ([Id], [CountryCode], [CountryDescription], [Region]) VALUES (40, N'CH', N'Switzerland', N'Europe Excl UK')

		INSERT [TradingData].[Country] ([Id], [CountryCode], [CountryDescription], [Region]) VALUES (41, N'TW', N'Taiwan', N'Asia Excl Japan')
		
		INSERT [TradingData].[Country] ([Id], [CountryCode], [CountryDescription], [Region]) VALUES (42, N'TR', N'Turkey', N'Europe Excl UK')

		INSERT [TradingData].[Country] ([Id], [CountryCode], [CountryDescription], [Region]) VALUES (43, N'US', N'United States', N'North America')
		
		INSERT [TradingData].[Country] ([Id], [CountryCode], [CountryDescription], [Region]) VALUES (44, N'DE', N'Germany', N'Europe Excl UK')
		
		INSERT [TradingData].[Country] ([Id], [CountryCode], [CountryDescription], [Region]) VALUES (45, N'GB', N'Great Britain', N'United Kingdom')
		
		INSERT [TradingData].[Country] ([Id], [CountryCode], [CountryDescription], [Region]) VALUES (46, N'ES', N'Spain', N'Europe Excl UK')
		
		INSERT [TradingData].[Country] ([Id], [CountryCode], [CountryDescription], [Region]) VALUES (47, N'SE', N'Sweden', N'Europe Excl UK')
		
		INSERT [TradingData].[Country] ([Id], [CountryCode], [CountryDescription], [Region]) VALUES (48, N'AE', N'United Arab Emirates (UAE)', N'Middle East')
		
		INSERT [TradingData].[Country] ([Id], [CountryCode], [CountryDescription], [Region]) VALUES (49, N'GI', N'Gibraltar', N'United Kingdom')
		
		INSERT [TradingData].[Country] ([Id], [CountryCode], [CountryDescription], [Region]) VALUES (50, N'VG', N'Virgin Islands; British', N'United Kingdom')

		INSERT [TradingData].[Country] ([Id], [CountryCode], [CountryDescription], [Region]) VALUES (51, N'UK', N'United Kingdom', N'United Kingdom')

		INSERT [TradingData].[Country] ([Id], [CountryCode], [CountryDescription], [Region]) VALUES (52, N'TH', N'Thailand', N'Asia Excl Japan')

		INSERT [TradingData].[Country] ([Id], [CountryCode], [CountryDescription], [Region]) VALUES (53, N'SG', N'Singapore', N'Asia Excl Japan')

		INSERT [TradingData].[Country] ([Id], [CountryCode], [CountryDescription], [Region]) VALUES (54, N'NZ', N'New Zealand', N'Australasia')

		INSERT [TradingData].[Country] ([Id], [CountryCode], [CountryDescription], [Region]) VALUES (55, N'MY', N'Malaysia', N'Asia Excl Japan')

		INSERT [TradingData].[Country] ([Id], [CountryCode], [CountryDescription], [Region]) VALUES (56, N'ID', N'Indonesia', N'Asia Excl Japan')

		INSERT [TradingData].[Country] ([Id], [CountryCode], [CountryDescription], [Region]) VALUES (57, N'KH', N'Cambodia', N'Asia Excl Japan')

		INSERT [TradingData].[Country] ([Id], [CountryCode], [CountryDescription], [Region]) VALUES (58, N'CY', N'Cyprus', N'Europe Excl UK')

		INSERT [TradingData].[Country] ([Id], [CountryCode], [CountryDescription], [Region]) VALUES (59, N'JE', N'Jersey', N'Europe Excl UK')

		INSERT [TradingData].[Country] ([Id], [CountryCode], [CountryDescription], [Region]) VALUES (60, N'GG', N'Guernsey', N'Europe Excl UK')

		INSERT [TradingData].[Country] ([Id], [CountryCode], [CountryDescription], [Region]) VALUES (61, N'IM', N'Isle of Man', N'United Kingdom')

		INSERT [TradingData].[Country] ([Id], [CountryCode], [CountryDescription], [Region]) VALUES (62, N'MX', N'Mexico', N'Latin America')

		INSERT [TradingData].[Country] ([Id], [CountryCode], [CountryDescription], [Region]) VALUES (63, N'ZA', N'South Africa', N'Africa')

		INSERT [TradingData].[Country] ([Id], [CountryCode], [CountryDescription], [Region]) VALUES (64, N'MU', N'Mauritius', N'Africa')
		
		INSERT [TradingData].[Country] ([Id], [CountryCode], [CountryDescription], [Region]) VALUES (65, N'KR', N'Korea (South)', N'Asia Excl Japan')

		INSERT [TradingData].[Country] ([Id], [CountryCode], [CountryDescription], [Region]) VALUES (66, N'IE', N'Ireland Republic of', N'Europe Excl UK')

		SET IDENTITY_INSERT [TradingData].[Country] OFF
	END


declare @currencycount int;
select @currencycount =  COUNT(*) FROM [TradingData].[Currency]
IF (@currencycount = 0 )
BEGIN
SET IDENTITY_INSERT [TradingData].[Currency] ON
INSERT [TradingData].[Currency] ([Id], [Name], [ISOCode], [Symbol]) VALUES (0,N'Unassigned', N'NaN','-')
INSERT [TradingData].[Currency] ([Id], [Name], [ISOCode], [Symbol]) VALUES (1,'Unassigned','','-')
SET IDENTITY_INSERT [TradingData].[Currency] OFF
INSERT [TradingData].[Currency] ([Name], [ISOCode], [Symbol]) VALUES (N'Afghanistan Afghani', N'AFN', N'؋')
INSERT [TradingData].[Currency] ([Name], [ISOCode], [Symbol]) VALUES (N'Argentina Peso', N'ARS', N'$')
INSERT [TradingData].[Currency] ([Name], [ISOCode], [Symbol]) VALUES (N'Aruba Guilder', N'AWG', N'ƒ')
INSERT [TradingData].[Currency] ([Name], [ISOCode], [Symbol]) VALUES (N'Australia Dollar', N'AUD', N'$')
INSERT [TradingData].[Currency] ([Name], [ISOCode], [Symbol]) VALUES (N'Bahamas Dollar', N'BSD', N'$')
INSERT [TradingData].[Currency] ([Name], [ISOCode], [Symbol]) VALUES (N'Barbados Dollar', N'BBD', N'$')
INSERT [TradingData].[Currency] ([Name], [ISOCode], [Symbol]) VALUES (N'Belgian Franc', N'BEF', N'₣')
INSERT [TradingData].[Currency] ([Name], [ISOCode], [Symbol]) VALUES (N'Bermuda Dollar', N'BMD', N'$')
INSERT [TradingData].[Currency] ([Name], [ISOCode], [Symbol]) VALUES (N'Botswana Pula', N'BWP', N'P')
INSERT [TradingData].[Currency] ([Name], [ISOCode], [Symbol]) VALUES (N'Brazilian real', N'BRL','R$')
INSERT [TradingData].[Currency] ([Name], [ISOCode], [Symbol]) VALUES (N'Brunei Darussalam Dollar', N'BND', N'$')
INSERT [TradingData].[Currency] ([Name], [ISOCode], [Symbol]) VALUES (N'Cambodia Riel', N'KHR', N'៛')
INSERT [TradingData].[Currency] ([Name], [ISOCode], [Symbol]) VALUES (N'Canada Dollar', N'CAD', N'$')
INSERT [TradingData].[Currency] ([Name], [ISOCode], [Symbol]) VALUES (N'Cayman Islands Dollar', N'KYD', N'$')
INSERT [TradingData].[Currency] ([Name], [ISOCode], [Symbol]) VALUES (N'Chile Peso', N'CLP', N'$')
INSERT [TradingData].[Currency] ([Name], [ISOCode], [Symbol]) VALUES (N'China Yuan Renminbi', N'CNY', N'¥')
INSERT [TradingData].[Currency] ([Name], [ISOCode], [Symbol]) VALUES (N'Colombia Peso', N'COP', N'$')
INSERT [TradingData].[Currency] ([Name], [ISOCode], [Symbol]) VALUES (N'Costa Rica Colon', N'CRC', N'₡')
INSERT [TradingData].[Currency] ([Name], [ISOCode], [Symbol]) VALUES (N'Cuba Peso', N'CUP', N'₱')
INSERT [TradingData].[Currency] ([Name], [ISOCode], [Symbol]) VALUES (N'East Caribbean Dollar', N'XCD', N'$')
INSERT [TradingData].[Currency] ([Name], [ISOCode], [Symbol]) VALUES (N'Egypt Pound', N'EGP', N'£')
INSERT [TradingData].[Currency] ([Name], [ISOCode], [Symbol]) VALUES (N'El Salvador Colon', N'SVC', N'$')
INSERT [TradingData].[Currency] ([Name], [ISOCode], [Symbol]) VALUES (N'Euro Member Countries', N'EUR', N'€')
INSERT [TradingData].[Currency] ([Name], [ISOCode], [Symbol]) VALUES (N'Falkland Islands (Malvinas) Pound', N'FKP', N'£')
INSERT [TradingData].[Currency] ([Name], [ISOCode], [Symbol]) VALUES (N'Fiji Dollar', N'FJD', N'$')
INSERT [TradingData].[Currency] ([Name], [ISOCode], [Symbol]) VALUES (N'Danish Krone', N'DKK', N'kr')
INSERT [TradingData].[Currency] ([Name], [ISOCode], [Symbol]) VALUES (N'Ghana Cedis', N'GHC', N'¢')
INSERT [TradingData].[Currency] ([Name], [ISOCode], [Symbol]) VALUES (N'Gibraltar Pound', N'GIP', N'£')
INSERT [TradingData].[Currency] ([Name], [ISOCode], [Symbol]) VALUES (N'Guatemala Quetzal', N'GTQ', N'Q')
INSERT [TradingData].[Currency] ([Name], [ISOCode], [Symbol]) VALUES (N'Guernsey Pound', N'GGP', N'£')
INSERT [TradingData].[Currency] ([Name], [ISOCode], [Symbol]) VALUES (N'Guyana Dollar', N'GYD', N'$')
INSERT [TradingData].[Currency] ([Name], [ISOCode], [Symbol]) VALUES (N'Honduras Lempira', N'HNL', N'L')
INSERT [TradingData].[Currency] ([Name], [ISOCode], [Symbol]) VALUES (N'Hong Kong Dollar', N'HKD', N'$')
INSERT [TradingData].[Currency] ([Name], [ISOCode], [Symbol]) VALUES (N'India Rupee', N'INR', N' ')
INSERT [TradingData].[Currency] ([Name], [ISOCode], [Symbol]) VALUES (N'Iran Rial', N'IRR', N'﷼')
INSERT [TradingData].[Currency] ([Name], [ISOCode], [Symbol]) VALUES (N'Isle of Man Pound', N'IMP', N'£')
INSERT [TradingData].[Currency] ([Name], [ISOCode], [Symbol]) VALUES (N'Israel Shekel', N'ILS', N'₪')
INSERT [TradingData].[Currency] ([Name], [ISOCode], [Symbol]) VALUES (N'Japan Yen', N'JPY', N'¥')
INSERT [TradingData].[Currency] ([Name], [ISOCode], [Symbol]) VALUES (N'Jersey Pound', N'JEP', N'£')
INSERT [TradingData].[Currency] ([Name], [ISOCode], [Symbol]) VALUES (N'Korea (North) Won', N'KPW', N'₩')
INSERT [TradingData].[Currency] ([Name], [ISOCode], [Symbol]) VALUES (N'Korea (South) Won', N'KRW', N'₩')
INSERT [TradingData].[Currency] ([Name], [ISOCode], [Symbol]) VALUES (N'Laos Kip', N'LAK', N'₭')
INSERT [TradingData].[Currency] ([Name], [ISOCode], [Symbol]) VALUES (N'Lebanon Pound', N'LBP', N'£')
INSERT [TradingData].[Currency] ([Name], [ISOCode], [Symbol]) VALUES (N'Liberia Dollar', N'LRD', N'$')
INSERT [TradingData].[Currency] ([Name], [ISOCode], [Symbol]) VALUES (N'Mauritius Rupee', N'MUR', N'₨')
INSERT [TradingData].[Currency] ([Name], [ISOCode], [Symbol]) VALUES (N'Mexico Peso', N'MXN', N'$')
INSERT [TradingData].[Currency] ([Name], [ISOCode], [Symbol]) VALUES (N'Mongolia Tughrik', N'MNT', N'₮')
INSERT [TradingData].[Currency] ([Name], [ISOCode], [Symbol]) VALUES (N'Namibia Dollar', N'NAD', N'$')
INSERT [TradingData].[Currency] ([Name], [ISOCode], [Symbol]) VALUES (N'Nepal Rupee', N'NPR', N'₨')
INSERT [TradingData].[Currency] ([Name], [ISOCode], [Symbol]) VALUES (N'Netherlands Antilles Guilder', N'ANG', N'ƒ')
INSERT [TradingData].[Currency] ([Name], [ISOCode], [Symbol]) VALUES (N'New Zealand Dollar', N'NZD', N'$')
INSERT [TradingData].[Currency] ([Name], [ISOCode], [Symbol]) VALUES (N'Nigeria Naira', N'NGN', N'₦')
INSERT [TradingData].[Currency] ([Name], [ISOCode], [Symbol]) VALUES (N'Norwegian Krone', N'NOK', N'kr')
INSERT [TradingData].[Currency] ([Name], [ISOCode], [Symbol]) VALUES (N'Oman Rial', N'OMR', N'﷼')
INSERT [TradingData].[Currency] ([Name], [ISOCode], [Symbol]) VALUES (N'Pakistan Rupee', N'PKR', N'₨')
INSERT [TradingData].[Currency] ([Name], [ISOCode], [Symbol]) VALUES (N'Philippines Peso', N'PHP', N'₱')
INSERT [TradingData].[Currency] ([Name], [ISOCode], [Symbol]) VALUES (N'Qatar Riyal', N'QAR', N'﷼')
INSERT [TradingData].[Currency] ([Name], [ISOCode], [Symbol]) VALUES (N'Saint Helena Pound', N'SHP', N'£')
INSERT [TradingData].[Currency] ([Name], [ISOCode], [Symbol]) VALUES (N'Saudi Arabia Riyal', N'SAR', N'﷼')
INSERT [TradingData].[Currency] ([Name], [ISOCode], [Symbol]) VALUES (N'Seychelles Rupee', N'SCR', N'₨')
INSERT [TradingData].[Currency] ([Name], [ISOCode], [Symbol]) VALUES (N'Singapore Dollar', N'SGD', N'$')
INSERT [TradingData].[Currency] ([Name], [ISOCode], [Symbol]) VALUES (N'Solomon Islands Dollar', N'SBD', N'$')
INSERT [TradingData].[Currency] ([Name], [ISOCode], [Symbol]) VALUES (N'Somalia Shilling', N'SOS', N'S')
INSERT [TradingData].[Currency] ([Name], [ISOCode], [Symbol]) VALUES (N'South Africa Rand', N'ZAR', N'R')
INSERT [TradingData].[Currency] ([Name], [ISOCode], [Symbol]) VALUES (N'Sri Lanka Rupee', N'LKR', N'₨')
INSERT [TradingData].[Currency] ([Name], [ISOCode], [Symbol]) VALUES (N'Suriname Dollar', N'SRD', N'$')
INSERT [TradingData].[Currency] ([Name], [ISOCode], [Symbol]) VALUES (N'Syria Pound', N'SYP', N'£')
INSERT [TradingData].[Currency] ([Name], [ISOCode], [Symbol]) VALUES (N'Swiss Franc', N'CHF', N'₣')
INSERT [TradingData].[Currency] ([Name], [ISOCode], [Symbol]) VALUES (N'Swedish Krona', N'SEK', N'kr')
INSERT [TradingData].[Currency] ([Name], [ISOCode], [Symbol]) VALUES (N'Thailand Baht', N'THB', N'฿')
INSERT [TradingData].[Currency] ([Name], [ISOCode], [Symbol]) VALUES (N'Turkey Lira', N'TRY', N'TR')
INSERT [TradingData].[Currency] ([Name], [ISOCode], [Symbol]) VALUES (N'Turkey Lira', N'TRL', N'₤')
INSERT [TradingData].[Currency] ([Name], [ISOCode], [Symbol]) VALUES (N'Tuvalu Dollar', N'TVD', N'$')
INSERT [TradingData].[Currency] ([Name], [ISOCode], [Symbol]) VALUES (N'Ukraine Hryvna', N'UAH', N'₴')
INSERT [TradingData].[Currency] ([Name], [ISOCode], [Symbol]) VALUES (N'United Kingdom Pound', N'GBP', N'£')
INSERT [TradingData].[Currency] ([Name], [ISOCode], [Symbol]) VALUES (N'United States Dollar', N'USD', N'$')
INSERT [TradingData].[Currency] ([Name], [ISOCode], [Symbol]) VALUES (N'Viet Nam Dong', N'VND', N'₫')
INSERT [TradingData].[Currency] ([Name], [ISOCode], [Symbol]) VALUES (N'Yemen Rial', N'YER', N'﷼')
INSERT [TradingData].[Currency] ([Name], [ISOCode], [Symbol]) VALUES (N'Zloty', N'PLN', N'zł')
INSERT [TradingData].[Currency] ([Name], [ISOCode], [Symbol]) VALUES (N'Ringgit Malais', N'MYR', N'RM')
INSERT [TradingData].[Currency] ([Name], [ISOCode], [Symbol]) VALUES (N'Czech koruna', N'CZK', N'Kč')
INSERT [TradingData].[Currency] ([Name], [ISOCode], [Symbol]) VALUES (N'Forint', N'HUF', N'Ft')
INSERT [TradingData].[Currency] ([Name], [ISOCode], [Symbol]) VALUES (N'Russian Ruble', N'RUB', N'p')
INSERT [TradingData].[Currency] ([Name], [ISOCode], [Symbol]) VALUES (N'Indonesian rupiah', N'IDR', N'Rp')
INSERT [TradingData].[Currency] ([Name], [ISOCode], [Symbol]) VALUES (N'New Taiwan Dollar', N'TWD', N'NT')
END
IF NOT EXISTS (SELECT * FROM [Settings].[Parameters] ) 
BEGIN
	insert into  [Settings].[Parameters] (Created,Type,UserId,Value) values(GETDATE(),'InvestmentIntelligence.iComply.Data.Settings.CalculationSettings',1,'{"EventHalfLife":{"Range":{"Min":5.0,"Max":50.0},"Value":30.0,"Step":1.0},"EventSigmaMoveThreshold":{"Range":{"Min":1.0,"Max":5.0},"Value":3.0,"Step":0.25},"EventSurveillanceWindow":{"Range":{"Min":1.0,"Max":30.0},"Value":30.0,"Step":1.0},"EventExtremeSigmaMoveThreshold":{"Range":{"Min":0.0,"Max":50.0},"Value":10.0,"Step":0.5},"EventExtremeSurveillanceWindow":{"Range":{"Min":0.0,"Max":356.0},"Value":60.0,"Step":1.0},"Parameters":{"MarketCapMidLL":1000000.00,"MarketCapLargeLL":10000000.00,"EntryStyleMomentumThreshold":0.00,"EntryStyleContrarianThreshold":0.00,"MarketCapCurrencyId":1,"EntryStyleWindowInDays":0,"ExpectedReturnWindowInDays":0,"VolatilityHalflifeInDays":0,"VolatilityWindowInDays":0},"HeatParams":{"HoldingPeriod":{"Range":{"Min":0.0,"Max":10.0},"Step":1.0,"Weight":3.0,"Values":[{"Key":0.0,"Value":1.0},{"Key":0.1,"Value":0.75},{"Key":0.2,"Value":0.5},{"Key":0.3,"Value":0.0}]},"ExcessEventReturn":{"Range":{"Min":0.0,"Max":10.0},"Step":1.0,"Weight":5.0,"Values":[{"Key":0.0,"Value":0.0},{"Key":3.0,"Value":0.5},{"Key":4.0,"Value":0.75},{"Key":5.0,"Value":1.0}]},"PositionSize":{"Range":{"Min":0.0,"Max":10.0},"Step":1.0,"Weight":9.0,"Values":[{"Key":0.0,"Value":1.0},{"Key":0.1,"Value":0.75},{"Key":0.2,"Value":0.5},{"Key":0.3,"Value":0.0}]},"TradeSize":{"Range":{"Min":0.0,"Max":10.0},"Step":1.0,"Weight":10.0,"Values":[{"Key":0.0,"Value":1.0},{"Key":0.1,"Value":0.75},{"Key":0.2,"Value":0.5},{"Key":0.3,"Value":0.0}]},"PriorReturns":{"Range":{"Min":0.0,"Max":10.0},"Step":1.0,"Weight":7.0,"Values":[{"Key":0.0,"Value":1.0},{"Key":0.1,"Value":0.75},{"Key":0.2,"Value":0.5},{"Key":0.3,"Value":0.0}]},"TradeType":{"Range":{"Min":0.0,"Max":10.0},"Step":1.0,"Weight":3.0,"Values":[{"Key":0.0,"Value":1.0},{"Key":0.1,"Value":0.75},{"Key":0.2,"Value":0.5},{"Key":0.3,"Value":0.0}]}}}')
END

IF NOT EXISTS (SELECT name FROM master.sys.server_principals WHERE name = 'tradeEQ')
BEGIN
    CREATE LOGIN [tradeEQ] WITH PASSWORD = 'tradeEQ'
END


IF NOT EXISTS    (SELECT name FROM syslogins WHERE name = 'tradeEQ')
BEGIN
    CREATE USER tradeEQ FOR LOGIN tradeEQ 
	EXEC sp_addrolemember N'db_owner', N'tradeEQ'
END

UPDATE [iPerform].[TemplateAnswer]
SET [CreatedOn] = AddedDt
WHERE CreatedOn = '1990-08-18'
GO

GO
PRINT N'Checking existing data against newly created constraints';



GO
ALTER TABLE [Analytics].[TradedQuantity] WITH CHECK CHECK CONSTRAINT [FK_TradedQuantity_Investment];

ALTER TABLE [Analytics].[InvestmentSerie] WITH CHECK CHECK CONSTRAINT [FK_InvestmentSerie_Investment];

ALTER TABLE [Analytics].[InvestmentSerie] WITH CHECK CHECK CONSTRAINT [FK_InvestmentSerie_Security];

ALTER TABLE [Analytics].[Investment] WITH CHECK CHECK CONSTRAINT [FK_Investment_Idea];

ALTER TABLE [DataConsistency].[SuspiciousExposure] WITH CHECK CHECK CONSTRAINT [FK_Exposure_FundBookEntity];

ALTER TABLE [DataConsistency].[SuspiciousExposure] WITH CHECK CHECK CONSTRAINT [FK_Exposure_Security];

ALTER TABLE [iComply].[Comments] WITH CHECK CHECK CONSTRAINT [FK_Comments_CompliancePeriod];

ALTER TABLE [iComply].[Comments] WITH CHECK CHECK CONSTRAINT [FK_Comments_Comments];

ALTER TABLE [iComply].[Comments] WITH CHECK CHECK CONSTRAINT [FK_Comments_User];

ALTER TABLE [iComply].[IncludedEventSecurityClass] WITH CHECK CHECK CONSTRAINT [FK_IncludedEventSecurityClass_SecurityClass];

ALTER TABLE [iComply].[ComplianceEvent] WITH CHECK CHECK CONSTRAINT [FK_ComplianceEvent_Security];

ALTER TABLE [iComply].[CommentToFileUpload] WITH CHECK CHECK CONSTRAINT [FK_CommentToFileUpload_Comment];

ALTER TABLE [iComply].[CommentToFileUpload] WITH CHECK CHECK CONSTRAINT [FK_CommentToFileUpload_FileUpload];

ALTER TABLE [iComply].[SuspiciousActivity] WITH CHECK CHECK CONSTRAINT [FK_SuspiciousActivityToComplianceEvent];

ALTER TABLE [iComply].[SuspiciousActivity] WITH CHECK CHECK CONSTRAINT [FK_SuspiciousActivityToFundBookEntityId];

ALTER TABLE [iComply].[TradeUnderSurveillance] WITH CHECK CHECK CONSTRAINT [FK_TradeUnderSurveillanceToSuspiciousActivity];

ALTER TABLE [iComply].[TradeUnderSurveillance] WITH CHECK CHECK CONSTRAINT [FK_TradeUnderSurveillance_Trade];

ALTER TABLE [iComply].[TradeHeatItems] WITH CHECK CHECK CONSTRAINT [FK_TradeHeatItem_iComply.TradeUnderSurveillance];

ALTER TABLE [iComply].[CompliancePeriodToSuspiciousActivity] WITH CHECK CHECK CONSTRAINT [FK_CompliancePeriodToSuspiciousActivity_CompliancePeriod];

ALTER TABLE [iComply].[CompliancePeriodToSuspiciousActivity] WITH CHECK CHECK CONSTRAINT [FK_CompliancePeriodToSuspiciousActivity_SuspiciousActivity];

ALTER TABLE [iComply].[CompliancePeriod] WITH CHECK CHECK CONSTRAINT [FK_CompliancePeriod_User];

ALTER TABLE [iPerform].[Layout] WITH CHECK CHECK CONSTRAINT [FK_Layout_User];

ALTER TABLE [iPerform].[Template2PossibleAnswer] WITH CHECK CHECK CONSTRAINT [FK_Template2PossibleAnswer_PossibleAnswer];

ALTER TABLE [iPerform].[Template2PossibleAnswer] WITH CHECK CHECK CONSTRAINT [FK_Template2PossibleAnswer_Template];

ALTER TABLE [iPerform].[Template] WITH CHECK CHECK CONSTRAINT [FK_Template_Template];

ALTER TABLE [iPerform].[IdeaToSecurity] WITH CHECK CHECK CONSTRAINT [FK_IdeaToSecurity_Idea];

ALTER TABLE [iPerform].[Question] WITH CHECK CHECK CONSTRAINT [FK_Question_QuestionGroup];

ALTER TABLE [iPerform].[Question] WITH CHECK CHECK CONSTRAINT [FK_Question_Question];

ALTER TABLE [iPerform].[PossibleAnswer] WITH CHECK CHECK CONSTRAINT [FK_PossibleAnswer_Question];

ALTER TABLE [iPerform].[PossibleAnswer] WITH CHECK CHECK CONSTRAINT [FK_PossibleAnswer_PossibleAnswer];

ALTER TABLE [iPerform].[AnalysisGroup2User] WITH CHECK CHECK CONSTRAINT [FK_AnalysisGroup2User_AnalysisGroup];

ALTER TABLE [iPerform].[AnalysisGroup2User] WITH CHECK CHECK CONSTRAINT [FK_AnalysisGroup2User_User];

ALTER TABLE [iPerform].[AnalysisGroup2FundBookEntity] WITH CHECK CHECK CONSTRAINT [FK_AnalysisGroup2FundBookEntity_AnalysisGroup];

ALTER TABLE [iPerform].[AnalysisGroup2FundBookEntity] WITH CHECK CHECK CONSTRAINT [FK_AnalysisGroup2FundBookEntity_FundBookEntity];

ALTER TABLE [iPerform].[Template2QuestionGroup] WITH CHECK CHECK CONSTRAINT [FK_Template2QuestionGroup_Template];

ALTER TABLE [iPerform].[Template2Question] WITH CHECK CHECK CONSTRAINT [FK_Template2Question_Template];

ALTER TABLE [iPerform].[Template2Question] WITH CHECK CHECK CONSTRAINT [FK_Template2Question_Question];

ALTER TABLE [iPerform].[IdeaToTrade] WITH CHECK CHECK CONSTRAINT [FK_IdeaToTrade_Idea];

ALTER TABLE [iPerform].[TemplateAnswer] WITH CHECK CHECK CONSTRAINT [FK_TemplateAnswer_Template];

ALTER TABLE [iPerform].[Answer] WITH CHECK CHECK CONSTRAINT [FK_Answer_PossibleAnswer];

ALTER TABLE [iPerform].[Answer] WITH CHECK CHECK CONSTRAINT [FK_Answer_Question];

ALTER TABLE [iPerform].[Answer] WITH CHECK CHECK CONSTRAINT [FK_Answer_TemplateAnswer];

ALTER TABLE [Membership].[User2Role] WITH CHECK CHECK CONSTRAINT [FK_User2Role_Role];

ALTER TABLE [Membership].[User2Role] WITH CHECK CHECK CONSTRAINT [FK_User2Role_User];

ALTER TABLE [Membership].[User] WITH CHECK CHECK CONSTRAINT [FK_User_Contact];

ALTER TABLE [Membership].[Role] WITH CHECK CHECK CONSTRAINT [FK_Role_Entity];

ALTER TABLE [Reports].[UserActionDetails] WITH CHECK CHECK CONSTRAINT [FK_UserActionDetails_UserAction];

ALTER TABLE [Reports].[UserAction] WITH CHECK CHECK CONSTRAINT [FK_UserAction_User];

ALTER TABLE [TradingData].[BenchmarkList] WITH CHECK CHECK CONSTRAINT [FK_BenchmarkList_Security];

ALTER TABLE [TradingData].[TradeConsistencyCheck] WITH CHECK CHECK CONSTRAINT [FK_TradeConsistencyCheck_Security];

ALTER TABLE [TradingData].[TradeConsistencyCheck] WITH CHECK CHECK CONSTRAINT [FK_TradeConsistencyCheck_FundbookEntity];

ALTER TABLE [TradingData].[FundBookTradeLastUpdate] WITH CHECK CHECK CONSTRAINT [FK_FundBookTradeLastUpdate_FundBookEntityId];

ALTER TABLE [TradingData].[OriginalPosition] WITH CHECK CHECK CONSTRAINT [FK_OriginalPosition_Security];

ALTER TABLE [TradingData].[OriginalPosition] WITH CHECK CHECK CONSTRAINT [FK_OriginalPosition_FundBookEntity];

ALTER TABLE [TradingData].[FxSerie] WITH CHECK CHECK CONSTRAINT [FK_FxSerie_Currency];

ALTER TABLE [TradingData].[FundBookEntitySerie] WITH CHECK CHECK CONSTRAINT [FK_FundBookEntitySerie_Fundbook];

ALTER TABLE [TradingData].[FundBookEntity] WITH CHECK CHECK CONSTRAINT [FK_TradingData.Book_TradingData.Book];

ALTER TABLE [TradingData].[FundBookEntity] WITH CHECK CHECK CONSTRAINT [FK_FundBookEntity_Currency];

ALTER TABLE [TradingData].[ExcessTimeSerie] WITH CHECK CHECK CONSTRAINT [FK_ExcessTimeSerie_Security];

ALTER TABLE [TradingData].[ExcessTimeSerie] WITH CHECK CHECK CONSTRAINT [FK_ExcessTimeSerie_Benchmark];

ALTER TABLE [TradingData].[BookSecurityToBenchmark] WITH CHECK CHECK CONSTRAINT [FK_BookSecurityToBenchmark_FundBookEntity];

ALTER TABLE [TradingData].[BookSecurityToBenchmark] WITH CHECK CHECK CONSTRAINT [FK_BookSecurityToBenchmark_Security_Security];

ALTER TABLE [TradingData].[BookBenchmarkType] WITH CHECK CHECK CONSTRAINT [FK_BookBenchmarkType_FundBookEntity];

ALTER TABLE [TradingData].[Trade] WITH CHECK CHECK CONSTRAINT [FK_Trade_Currency];

ALTER TABLE [TradingData].[Trade] WITH CHECK CHECK CONSTRAINT [FK_Trade_Security];

ALTER TABLE [TradingData].[Trade] WITH CHECK CHECK CONSTRAINT [FK_Trade_FundbookEntity];

ALTER TABLE [TradingData].[TimeSerie] WITH CHECK CHECK CONSTRAINT [FK_TimeSerie_Security];

ALTER TABLE [TradingData].[Security] WITH CHECK CHECK CONSTRAINT [FK_Security_SecurityClass];

ALTER TABLE [TradingData].[Security] WITH CHECK CHECK CONSTRAINT [FK_Security_Country];

ALTER TABLE [TradingData].[Security] WITH CHECK CHECK CONSTRAINT [FK_Security_Currency];

ALTER TABLE [TradingData].[Security] WITH CHECK CHECK CONSTRAINT [FK_Security_EconomicSector];


GO
CREATE TABLE [#__checkStatus] (
    id           INT            IDENTITY (1, 1) PRIMARY KEY CLUSTERED,
    [Schema]     NVARCHAR (256),
    [Table]      NVARCHAR (256),
    [Constraint] NVARCHAR (256)
);

SET NOCOUNT ON;


GO
DECLARE tableconstraintnames CURSOR LOCAL FORWARD_ONLY
    FOR SELECT SCHEMA_NAME([schema_id]),
               OBJECT_NAME([parent_object_id]),
               [name],
               0
        FROM   [sys].[objects]
        WHERE  [parent_object_id] IN (OBJECT_ID(N'Analytics.IdeaToTrade'), OBJECT_ID(N'Analytics.IdeaToSecurity'), OBJECT_ID(N'Analytics.TracePoint'))
               AND [type] IN (N'F', N'C')
                   AND [object_id] IN (SELECT [object_id]
                                       FROM   [sys].[check_constraints]
                                       WHERE  [is_not_trusted] <> 0
                                       UNION
                                       SELECT [object_id]
                                       FROM   [sys].[foreign_keys]
                                       WHERE  [is_not_trusted] <> 0);

DECLARE @schemaname AS NVARCHAR (256);

DECLARE @tablename AS NVARCHAR (256);

DECLARE @checkname AS NVARCHAR (256);

DECLARE @is_not_trusted AS INT;

DECLARE @statement AS NVARCHAR (1024);

BEGIN TRY
    OPEN tableconstraintnames;
    FETCH tableconstraintnames INTO @schemaname, @tablename, @checkname, @is_not_trusted;
    WHILE @@fetch_status = 0
        BEGIN
            PRINT N'Checking constraint:' + @checkname + N' [' + @schemaname + N'].[' + @tablename + N']';
            SET @statement = N'ALTER TABLE [' + @schemaname + N'].[' + @tablename + N'] WITH ' + CASE @is_not_trusted WHEN 0 THEN N'CHECK' ELSE N'NOCHECK' END + N' CHECK CONSTRAINT [' + @checkname + N']';
            BEGIN TRY
                EXECUTE [sp_executesql] @statement;
            END TRY
            BEGIN CATCH
                INSERT  [#__checkStatus] ([Schema], [Table], [Constraint])
                VALUES                  (@schemaname, @tablename, @checkname);
            END CATCH
            FETCH tableconstraintnames INTO @schemaname, @tablename, @checkname, @is_not_trusted;
        END
END TRY
BEGIN CATCH
    PRINT ERROR_MESSAGE();
END CATCH

IF CURSOR_STATUS(N'LOCAL', N'tableconstraintnames') >= 0
    CLOSE tableconstraintnames;

IF CURSOR_STATUS(N'LOCAL', N'tableconstraintnames') = -1
    DEALLOCATE tableconstraintnames;

SELECT N'Constraint verification failed:' + [Schema] + N'.' + [Table] + N',' + [Constraint]
FROM   [#__checkStatus];

IF @@ROWCOUNT > 0
    BEGIN
        DROP TABLE [#__checkStatus];
        RAISERROR (N'An error occured while verifying constraints', 16, 127);
    END


GO
SET NOCOUNT OFF;

DROP TABLE [#__checkStatus];


GO
PRINT N'CREATE complete.';


GO

INSERT INTO [System].[RefactorLog] ([file-name],[runing-date],[description]) VALUES ('DataBaseUpdateScripts\20131115\01.sql',GETDATE(),'Create RefactorLogTable and system schema')
INSERT INTO [System].[RefactorLog] ([file-name],[runing-date],[description]) VALUES ('DataBaseUpdateScripts\20131115\02.sql',GETDATE(),'Drop Old Tables')
INSERT INTO [System].[RefactorLog] ([file-name],[runing-date],[description]) VALUES ('DataBaseUpdateScripts\20131115\03.sql',GETDATE(),'DROP Then CREATE Indexes')
INSERT INTO [System].[RefactorLog] ([file-name],[runing-date],[description]) VALUES ('DataBaseUpdateScripts\20131115\04.sql',GETDATE(),'UPDATE TABLES')
INSERT INTO [System].[RefactorLog] ([file-name],[runing-date],[description]) VALUES ('DataBaseUpdateScripts\20131115\05.sql',GETDATE(),'Update Stored Procedure')


